Monday, March 26, 2012

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,
FarazSELECT * from tbl t1 left outer join tbl t2 on t1.ID = t2.ParentID where t2.ParentID is null|||Not to be to fancy using having clause,

SELECT * FROM myTable WHERE ID not in (select ParentID from mytable)|||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|||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)"|||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|||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)|||Okay, if you insist...

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

Thanks everyone.|||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...|||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)

No comments:

Post a Comment