Select * from table that aren't in another table

[loydall]

Hello - I have 2 tables. table1 contains a list of people. Table2 contains a list of poeple made up of SOME of the people from table1.. (So table1 has more records than table2).

I need to select all the people from table1 that ARE NOT in table2..

I will be selecting using FirstName and LastName..

Any ideas?

 

[gmmastros]

There are various ways to do this. Here's how I would write the query.

Select Table1.FirstName,
       Table1.LastName
From   Table1
       Left Join Table2
         On  Table1.FirstName = Table2.FirstName
         And table1.LastName = Table2.LastName
Where  Table2.FirstName Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

You want to use a left join for this, and only select records with no match on the 'right' table.

like this:

Select table1.*
from table1
left join table2
on table1.FirstName = table2.FirstName
and table1.LastName = table2.LastName
where table2.LastName is null

Be warned this could produce some funky results if there is nothing better to join on than first and last name.

Hpe it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

Alex is right, it is a very bad idea to use the name as the field you join as on name is by deifinaiton not a unique identifier. Look in the phone book at John Smith and you will understand why this is a very poor design. If you ever get two people with the same name (a very common occurance) then you will not be able to tell which of the records in the child table belong to which inthe parent table. You really need to investigate whether you can add an id column to the parent table and then store that inthe child table instead of the naems.

Questions about posting. See faq183-874

 

[loydall]

Yes - thanks for your help, I know it's not ideal but unfortunately it's the only data I have to play with..

You solutions worked... thanks..