[SOLVED] Pulling records from one table not featured in another table

[Buchead]

Hello...

 

I should know the answer to this but can't seem to work it out.

 

Have 2 tables:

 

staff:

staffID - unique ID

staffName - name

 

depts:

deptID - unique ID

deptName - name

staffID - linked to staffID in `staff`

 

I want to pull out a list of all names from `staff` who are not listed in `depts`. Can pull out the list of staff in depts but not the other way round.

 

Any pointers most appreciated.....

[fenway]

You want a left join to include non-matching rows, and then filter only those that don't match (i.e. null-ed out):

 

select s.* from staff as s left join depts as d using ( staffID ) where d.staffID IS NULL

[Buchead]

Cool. Thanks for that.