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

    Unanswered: NOT EXISTS problem

    I have a table in which there are the following 2 columns: sitm and pitm.
    sitm is the child item, and pitm is the parent item. i would like to find all child items that are not parent items.

    I would have thought that the following query would do this:

    Code:
    select sitm from lhpms010
    where not exists (select pitm from lhpms010)
    Anybody have any suggestions why this doesn't work? There are certainly items in the sitm column that are not in the pitm column...

    steven.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about

    SELECT sitm FROM lhpms010
    MINUS
    SELECT pitm FROM lhpms010;

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    If your DBMS does not support the minus, then you could try a correlated subquery with your not exists predicate.

    Code:
    select sitm from lhpms010 a
    where not exists (select pitm from lhpms010 b
                             where b.pitm = a.sitm)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there were two additional really good alternative approaches posted into the exact same thread as this one in the sql server forum

    <sigh />
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2005
    Posts
    20
    Hi ,

    The MINUS query should work.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Nirmala
    The MINUS query should work.
    but MINUS is proprietary, and this is the non-proprietary SQL forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Try replacing MINUS by EXCEPT; that's standard SQL.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by steve_o
    select sitm from lhpms010
    where not exists (select pitm from lhpms010)
    Typically, in an EXISTS, the selected value (here pitm) is irrelevant.
    So what you wanted was a WHERE condition in your subquery, specifying that the sitm of the outer query appears in the pitm column of the subquery:
    Code:
    select sitm from lhpms010 AS x
    where not exists (select 1 from lhpms010 where pitm = x.sitm)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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