optional lookup table also has duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

optional lookup table also has duplicates

Author

Topic

LisaS
Starting Member

12 Posts

Posted - 2007-08-08 : 17:44:30

Table A has detail data.
Table B has a lookup value for one of the colums in Table A.

the problem:
1)Not all A records have a corresponding entry in Table B so a LEFT OUTER JOIN would appear to be necesary.
2)Table B has some duplicated rows on the key that joins back to A.

example data:
Table A
NAME TYPE_CD AMOUNT
AAA A1 100.00
BBB B1 100.00
CCC A1 200.00
DDD C1 300.00

Table B
TYPE_CD TYPE_DESCR USER
A1 A1STUFF ABC
A1 A1STUFF ABC1
B2 B1STUFF BCD

and we want to return:
NAME TYPE_DESCR AMT
AAA A1STUFF 100.00
BBB B1STUFF 100.00
CCC A1STUFF 200.00
DDD NULL 300.00

Sorry if this is simple, but it's been driving me nuts to get the distinct value and the LEFT OUTER logic together.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-08 : 17:51:47

You either have to clean up the data (and then constrain the data so it cannot happen again) or you need to provide the logic for determining how to match a row from Table A to a row in Table B.

It sounds like you get how to do the join. But, I'm not sure how we can help you determine your business logic..?

LisaS
Starting Member

12 Posts

Posted - 2007-08-08 : 18:00:39

sorry. I'm trying to help a colleague here and didn't give a full explanation. Apparently the lookup table cannot be cleaned up at this time and if it were, it cannot be guaranteed to not get duplicated again so he is trying to deal with it in place.

In the ideal world this would be fine:
SELECT
A.NAME NAME,
B.TYPE_DESC DESC,
A.AMT AMOUNT
FROM TABLE_A A, TABLE_B B
WHERE A.TYPE_CD = B.TYPE_CD

the two twists are:
not every A.TYPE_CD is represented in TABLE_B
and some TABLE_B rows are duplicated and we don't want two matches returning

so - we tried this for the first case (missing dependent table entry)
SELECT
A.NAME NAME,
B.TYPE_DESC DESC,
A.AMT AMOUNT
FROM TABLE_A A
LEFT OUTER JOIN TABLE_B B ON A.TYPE_CD = B.TYPE_CD

but we still have the duplicate problem and get multiple rows back
from table_B in some cases.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-08 : 18:32:13

Cool, sounds like you are almost there. :)

When you do the second option (LEFT OUTER JOIN) and two or more rows are returned for a given B.Type_CD, which one should be selected? Are there other columns that can be used to determine which one to pick, perhaps the newest one or something? It seems to me that, unless there is a way to distinctly select a row, your only option is to select a random row.

LisaS
Starting Member

12 Posts

Posted - 2007-08-08 : 18:33:08

apparently random row would be fine, there is no date to select the latest or first or any other characteristic to meaningfully distinquish between the duplicates.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-08 : 18:42:29

Here is one way to do it:

DECLARE @TableA TABLE ([NAME] VARCHAR(3), TYPE_CD VARCHAR(2), AMOUNT MONEY)

INSERT @TableA
SELECT 'AAA', 'A1', 100.00
UNION ALL SELECT 'BBB', 'B1', $100.00 
UNION ALL SELECT 'CCC', 'A1', $200.00 
UNION ALL SELECT 'DDD', 'C1', $300.00 

DECLARE @TableB TABLE (TYPE_CD VARCHAR(2), TYPE_DESCR VARCHAR(7), [USER] VARCHAR(4))

INSERT @TableB 
SELECT 'A1', 'A1STUFF', 'ABC' 
UNION ALL SELECT 'A1', 'A1STUFF', 'ABC1' 
UNION ALL SELECT 'B1', 'B1STUFF', 'BCD'


SELECT 
	A.[NAME],
	(SELECT TOP 1 B.TYPE_DESCR FROM @TableB AS B WHERE A.TYPE_CD = B.TYPE_CD) AS TYPE_DESCR,
	A.AMOUNT
FROM
	@TableA AS A

LisaS
Starting Member

12 Posts

Posted - 2007-08-08 : 18:53:10

looks perfect to me.
I tried your sample code and it works as needed.
I tried it with the application query and it works for the case where the rows are duplicated.
I have to find a test case to use to verify the NULL result case - but since it works in your sample for DDD I'm sure it will work as well in the application, but have to see with my own eyes.

Thank You.

Lisa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources