Select all data in one table that are not prsent in the second table?

[LittleNick]

Hi,
I have two tables tblData1 and tblData2 with 3 columns Id, First, Last, on both. tbldata1 is the full table and tbldata2 consist of selected records of tbldata1. Now I need to filter all the data out in tblData1 that are in tblData2. for ex:

tbl1 tbl2
1 3
2 5
3
4
5

I need to have a query or view that return tbl1 as
tbl1
1
2
4

Thanks for help

 

[bborissov]

SELECT Tbl1.*
FROM Tbl1
LEFT JOIN Tbl2 ON Tbl1.CommonField = Tbl2.CommonField
WHERE Tbl2.CommonField IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Please take a look at this recent MSDN thread and links from it

http://social.msdn.microsoft.com/Fo...l/thread/005d2234-5c2a-4adc-88a6-23e3a9100fb1

PluralSight Learning Library

 

[LittleNick]

Thanks bborissov. That was what I needed. It works great.
Thank you again.

 

[markros]

I stopped using and recommending LEFT JOIN approach after reading Gail Shaw article.

PluralSight Learning Library