Parent/child relationship - get only those no children

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Parent/child relationship - get only those no children

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-13 : 09:51:18

toby writes "HI all, I am not having any luck. I have a Parent/child issue. I have a single table - Categories. Any category can have children categories, and so on. The fields are CategoryId, ParentID, and Text. I have the table relating back to itself.

I want to get a result set that has all of the "childrenless categories" - Categories that have no child categories. I don't have a problem getting all the children for a category, but I can't get this problem.

Windows XP
Sql Server 2000 Service pack 1

I couldn't find any article on any website that addressed this issue. I have tried cursors with temp tables, but it hasn't solved my problem. If you can figure out a simple answer I'd be greatly appreciative.

Toby"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-13 : 09:53:26

There's a few methods that can achieve this, here's one:

SELECT A.* FROM Categories A
LEFT JOIN Categories B ON A.CategoryID=B.ParentID
WHERE B.ParentID IS NULL

You could also use NOT EXISTS or NOT IN to find those that don't have children, but others will be better able to post the syntax for them.

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-09-13 : 09:54:44

SELECT *
FROM categories
WHERE CategoryId not in
(SELECT ParentID FROM categories where ParentID is not null)

???

Edited by - andraax on 09/13/2002 09:59:09

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources