SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to do a not in a select statement

    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?
    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Is plumber a field in either tblHome, tblHomeOwner or tblHomeOwnerJobHistory tables?

    The query should look more like this:

    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the query should look more like this --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Code:
    id    address        
    1    1 main street
    2    1 batcave
    tblHomeOwner
    Code:
    id    homeid    name
    1    2    Bruce Wayne
    2    1    John Smith
    tblHomeOwnerJobHistory
    Code:
    id    homeownerid    job    
    1    1        plumber
    2    1        singer
    3    1        webdesign
    4    1        phone operator

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Oct 2001
    Posts
    359
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick, never knew about doing a second SELECT... thanks r937!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •