exclude duplicates in another table

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 2008 Forums

Transact-SQL (2008)

exclude duplicates in another table

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-06-14 : 15:41:58

I have a sql with a few joins that bring back a few records.
These records have a clustered PK (three fields).

There is another table that has the exact same clusted PK.

Pulling records from the first table works fine.
But I now want to exclude what exists in the second table.

In other words, if the second table has the exact PK from the first table, exclude it from the search.

Table 1 PK fields

1 2 3
3 5 7
4 2 7

Table 2 PK fields

1 2 3
4 2 7

So, the search should only bring back one record:
3 5 7

Been searching around but no luck on this one.

Thanks for any input and direction!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:44:37

[code]
SELECT *
FROM YourFirstTable t
WHERE NOT EXISTS(SELECT 1
FROM secondtable
WHERE PKCol1 = t.PKCol1
AND PKCol2 = t.PKCol2
AND PKCol3 = t.PKCol3
)
[/code]

PKCol1,PKCol2,PKCol3 are 3 columns that are part of clustered PK

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2012-06-14 : 15:45:55

NOT EXISTS.

Talk about a mental block!!!!

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:47:00

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources