exclude duplicate values - SQL Server Forums

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)

exclude duplicate values

Author

Topic

KlausEngelInc
Starting Member

8 Posts

Posted - 2009-10-09 : 11:54:08

I am joining three tables:

SELECT A.NAME,A.DESCRIPTION,B.CODE,C.NUMBER
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.ID = B.ID
INNER JOIN TABLE3 C ON A.ID = C.ID

table3 has multiple entries for ID and I just want to retrieve the first record that has a value for the number field. How do I need to modify my query?

Thank you,
K.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-09 : 11:58:34

replace your last join by this:
inner join
(select ID,max(Number) as Number from Table3 group by ID) as C ON A.ID = C.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

KlausEngelInc
Starting Member

8 Posts

Posted - 2009-10-09 : 12:01:45

Fantastic, this works great - thank you!

KlausEngelInc
Starting Member

8 Posts

Posted - 2009-10-09 : 12:15:20

quote:
Originally posted by webfred

replace your last join by this:
inner join
(select ID,max(Number) as Number from Table3 group by ID) as C ON A.ID = C.ID

Follow up question:

I need to replace my inner join with a left join as I don't have entries for all IDs from table 1 in table 3 yet I still need to return all records from table 1 whether or not they have a match in table 3.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-09 : 12:20:57

Yes you can do a left join and in your select list the number will be NULL.
If NULL is not fine for you the you can do
ISNULL(C.NUMBER,0) as NUMBER -- If Number is a numeric type
or you can do
ISNULL(C.NUMBER,'no entry') as NUMBER -- If it is varchar

No, you're never too old to Yak'n'Roll if you're too young to die.

KlausEngelInc
Starting Member

8 Posts

Posted - 2009-10-09 : 12:46:24

Thank you!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-09 : 12:52:24

welcome