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

    Unanswered: NOT EXISTS problem on SQL SERVER

    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)
    It doesn't return any results although there are certainly items in the sitm column that do not exist in the pitm column. I got the following solution from the SQL forum here, but as i am using SQLServer the 'minus' operator is not supported...

    Code:
    SELECT sitm FROM lhpms010
    MINUS
    SELECT pitm FROM lhpms010;
    Any body know how i can implement this query with SQL Server?

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Here's one way

    select l1.sitm
    from lhpms010 l1
    left join lhpms010 l2
    on l1.sitm = l2.pitm
    where l2.pitm is null

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Another

    select sitm
    from lhpms010
    where sitm not in (select pitm from lhpms010)

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Another

    select sitm
    from lhpms010
    where not exists (select * from lhpms010 l1 where lhpms010.sitm = l1.pitm)

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by peterlemonjello
    Another

    select sitm
    from lhpms010
    where not exists (select * from lhpms010 l1 where lhpms010.sitm = l1.pitm)
    What's with the vertical bars(|)?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    364
    It's a lowercase L

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Don't you hate sans-serif fonts?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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