Trying to do a not in a select statement

Databases
1

Trying to do a not in for a select statement but it returns all the rows in tblHomeOwnerJobHistory with the exception of plumber using the SQL below, I don’t want anything being retrieved for that Home. Any ideas?

select *
from tblHome, tblHomeOwner, tblHomeOwnerJobHistory
where tblHome.id=1
and tblHomeOwner.homeid = tblHome.id
and tblHomeOwner.id = tblHomeOwnerJobHistory.homeownerid
and tblHomeOwnerJobHistory.retired = 'NO'
and tblHomeOwnerJobHistory.Job not in plumber
2

Is plumber a field in either tblHome, tblHomeOwner or tblHomeOwnerJobHistory tables?

The query should look more like this:


select *
from tblHome, tblHomeOwner, tblHomeOwnerJobHistory
where tblHome.id=1
and tblHomeOwner.homeid = tblHome.id
and tblHomeOwner.id = tblHomeOwnerJobHistory.homeownerid
and tblHomeOwnerJobHistory.retired = 'NO'
and tblHomeOwnerJobHistory.Job not in ('plumber')
3

the query should look more like this –

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star 
  FROM tblHome
INNER
  JOIN tblHomeOwner
    ON tblHomeOwner.homeid = tblHome.id
INNER
  JOIN tblHomeOwnerJobHistory
    ON tblHomeOwnerJobHistory.homeownerid = tblHomeOwner.id
   AND tblHomeOwnerJobHistory.retired = 'NO'
   AND tblHomeOwnerJobHistory.Job not in ('plumber')
 WHERE tblHome.id = 1
4

Thanks for the suggestions, but this is retrieving the same results. If you think about the example table structures below, it will currently return rows that are not plumber (i.e. doing the query against tblHome=1 as suggested will bring back singer, phone operator, webdesign rows), but instead I only want it to return any tblHome=1 related information only if plumber isn’t listed against it.

tblHome

id    address        
1    1 main street
2    1 batcave

tblHomeOwner

id    homeid    name
1    2    Bruce Wayne
2    1    John Smith

tblHomeOwnerJobHistory

id    homeownerid    job    
1    1        plumber
2    1        singer
3    1        webdesign
4    1        phone operator
5 
SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star 
  FROM tblHome
INNER
  JOIN tblHomeOwner
    ON tblHomeOwner.homeid = tblHome.id
INNER
  JOIN tblHomeOwnerJobHistory
    ON tblHomeOwnerJobHistory.homeownerid = tblHomeOwner.id
   AND tblHomeOwnerJobHistory.retired = 'NO'
 WHERE tblHome.id = 1
   AND NOT EXISTS
       ( SELECT 'uh oh'
           FROM tblHomeOwnerJobHistory
          WHERE homeownerid = tblHomeOwner.id
            AND Job = 'plumber' )
6

That did the trick, never knew about doing a second SELECT… thanks r937!