Seemingly simple (NOT IN)

[MattSTech]

I have:

A table of employees - tblEmp
A table of things - tblthings
A table that associates the two - tblAssoc

I would like to see those employees not associated with things in the association table.

Should be something like below.

Select * from tblEmp
LEFT JOIN tblAssoc on tblAssoc.EmpID=tblEmp.ID
LEFT JOIN tblThings ON tblAssoc.ThingID=tblThings.ID
WHERE ----Psuedo Code Begins...
All of the employees that aren't associated with ThingID#2

And this gives me a list of all the guys NOT associated with thingID #2.

I have beat my head against the wall a bit trying to use NOT EXISTS, embedded select statements parsing out NULLS.
This really can't be that hard right??

Thanks in advance.

Matt

 

[MattSTech]

Aha.
FULL OUTER JOIN

That works...

 

[TamarGranor]

This is simpler:

SELECT * 
  FROM tblEmp
  WHERE tblEmp.ID 
    NOT IN (SELECT EmpID FROM tblAssoc WHERE ThingID=2)

Tamar

 

[wvdba]

if EmpID in tblAssoc has even one null value, then your query will return nothing.
better us "not exists". it's also more efficient.