Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    43

    Question Unanswered: Where Not Exists!?!

    I am trying to find all items in one table that do not exist in another table.

    The following query of my config table returns one row (which is '350'):
    SELECT config.pos from config

    The following query of my gsl table returns 47 rows (one of which is '350'):
    SELECT gsl.pos from gsl

    I do not understand why the follwing query returns no rows, when I would like it to return all rows (except for the one that is in the config table):
    SELECT gsl.pos from gsl
    WHERE NOT EXISTS (SELECT config.pos from config)

    Any ideas would be much appreciated..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's not working because the subquery returns all the rows in the config table and therefore the outer query doesn't return anything

    there are two ways to do what you want
    Code:
    select gsl.pos 
      from gsl
     where not exists 
         ( select pos 
             from config
            where pos = gsl.pos )
            
    select gsl.pos 
      from gsl
    left outer
      join config
        on gsl.pos 
         = config.pos
     where config.pos is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or if your DBMS supports it:

    SELECT gsl.pos from gsl
    MINUS
    SELECT config.pos from config

    (Sometimes it is EXCEPT rather than MINUS).

  4. #4
    Join Date
    Apr 2004
    Posts
    43
    Thanks both very much for your help!

    All solutions work great...

    Cheers,
    Steve.

Posting Permissions

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