Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    Gatineau, QC
    Posts
    4

    Unanswered: Easy query question

    Hello all,

    I have a table representing a tree structure with three fields, let's call them this for now:

    ID, Name, ParentID

    ID is an autonumber. ParentID points back to an ID.

    I need to find all those records that are not parents of other records. That is, I need to SELECT * FROM myTable WHERE (my ID is not in the ParentID column for any record.)

    I just need to translate that last bit ("my ID is not in the ParentID column for any record") into SQL.

    Any help would be greatly appreciated!!

    Thanks,
    Faraz

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    SELECT * from tbl t1 left outer join tbl t2 on t1.ID = t2.ParentID where t2.ParentID is null
    Last edited by rdjabarov; 12-18-03 at 18:10.

  3. #3
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Easy query question

    Not to be to fancy using having clause,

    SELECT * FROM myTable WHERE ID not in (select ParentID from mytable)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [sniped]

    SELECT c.*
    FROM myTable c
    LEFT JOIN myTable p
    ONc.ID = p.ParentId
    WHERE p.ParentId IS NULL

    OR

    SELECT c.*
    FROM myTable c
    WHERE NOT EXISTS
    ( SELECT * FROM myTable p
    WHERE c.ID = p.ParentId)

    OR

    SELECT *
    FROM myTable
    WHERE NOT IN
    ( SELECT ParentId FROM myTable)


    Your Choice

    [/sniped]
    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.

  5. #5
    Join Date
    Dec 2003
    Location
    Gatineau, QC
    Posts
    4
    Thanks! I knew this was easy, and I know I've done it before, but somehow kept getting either too many or too few results.

    Out of all your generous replies, I selected:

    "SELECT * FROM myTable WHERE ID NOT IN (select ParentID from MyTable)"
    Last edited by frazza; 12-18-03 at 17:20.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your choice to do it right or wrong.

    Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Your choice to do it right or wrong.

    Use the JOIN method like rdjabarov or Brett's first example. SQL Server may translate the other two examples into a JOIN before executing it, but if it does not then your resulting query plan is less efficient.

    blindman
    it depends......

    Change the IN one to this

    SELECT *
    FROM myTable
    WHERE ID NOT IN
    ( SELECT DISTINCT ParentId FROM myTable)

    If there is a low cardinality, this might be most effecient...

    You really need to a show plan to see which is most effecient (Blindman's right though, the Join usually wins)
    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.

  8. #8
    Join Date
    Dec 2003
    Location
    Gatineau, QC
    Posts
    4
    Okay, if you insist...

    I'm now using the Joins.... everything works swell.

    Thanks everyone.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Brett Kaiser
    You really need to a show plan to see which is most effecient
    Well actually I'd rather "insist" you do a SHOWPLAN on All three to see which one performs the best for you.

    This is normal SQL development....
    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.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    If you use "not in ("... Then make sure to either "set ansi_nulls off" or add "where parentid is not null" - otherwise, no records will be returned if there is a parentid which is null. For example:

    SELECT *
    FROM myTable
    WHERE ID NOT IN
    ( SELECT DISTINCT ParentId FROM myTable where parentid is not null)

Posting Permissions

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