DISTINCT and JOIN issue - 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)

DISTINCT and JOIN issue

Author

Topic

deniseaddy
Starting Member

12 Posts

Posted - 2008-11-21 : 11:54:35

Hi,

I have two tables, A and B.

I need all the information from Table A and its related data from Table B which references Table A with a

foreign key.. BUT, I only want unique records returned from Table A, even if Table B has multiple records that

refer to the same foreign key id.

How do I do this?

I've tired using "Select DISTINCT * from TableA" with left outer join to Table B but although it returns all

the associated data, Table A rows are sometimes duplicated.

Any ideas?

Thanks,

Denise

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 11:59:30

[code]SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY a.PK ORDER BY b.PK) AS Seq
FROM TableA a
LEFT JOIN TableB b
ON a.PK=b.FK
)t
WHERE Seq=1
[/code]

deniseaddy
Starting Member

12 Posts

Posted - 2008-11-24 : 08:48:05

That works great - thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 08:52:54

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources