Retrieve recs from table that isnt duplicated in another

[Denster]

I have two tables in an Access database, one to hold the main content and the other that acts as a link back to the same table. I need to retrieve everything that is in the first table that doesnt appear in the second table.
Example :-

Table 1:                             Table 2:
ID | Description                  Table1iD | LinkNum
2     Fred                           2          5
5     Bill                           2          6
6     Bob                            
7     George

So the query would return "Fred" and "George".
is there a SQL statement that will do this or is it a bit more complicated?

 

[r937]

i'm pretty sure you mean Fred and Bob, not Fred and George

select Table1.ID
     , Table1.Description
  from Table1
left outer
  join Table2
    on Table2.LinkNum = Table1.ID
 where Table2.LinkNum is null

r937.com | rudy.ca

 

[PHV]

Something like this ?
SELECT A.Description
FROM [Table 1] AS A LEFT JOIN [Table 2] AS B ON A.ID = B.LinkNum
WHERE B.LinkNum Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

sorry, yes, Fred and George, i am an idiot

r937.com | rudy.ca

 

[Denster]

Thanks PHV, that works just fine!.

Happy :-D