How to implement a "hasChildren" SELECT statement in SQL?

Question

Let's say I have the following table:

ID | parentID | MoreStuff
1  | -1       |  ...
2  |  1       |  ...
3  |  1       |  ...
4  |  2       |  ...
5  |  1       |  ...

How can I generate an SQL SELECT statement to find out if a specific row has children? In other words, I want to know if ID 1 has children, which in this case it has 3.

I'm not sure how to create the SQL statement:

SELECT ID, hasChildren FROM myTable;

What would be replaced for hasChildren in the above SQL SELECT statement?

Answer 1

Join the table on itself to find if it has children:

SELECT 
    parent.id as ID
,   case when count(child.id) > 0 then 1 else 0 end as hasChildren 
FROM       myTable parent
LEFT JOIN  myTable child
ON         child.parentID = parent.ID
GROUP BY   parent.id

Answer 2

If you know already know the parent ID, then the query is simple -- just select the number of rows with that parent ID.

SELECT count(*) FROM myTable where parentID = 1;

Answer 3

No group version:

SELECT MyTable.Id, CASE WHEN EXISTS 
    (SELECT TOP 1 1  --you can actually select anything you want here
     FROM MyTable MyTableCheck 
     WHERE MyTableCheck.ParentId = MyTable.Id
    ) THEN 1 ELSE 0 END AS HasRows
FROM MyTable

Answer 4

There are very valid answers to your question which will work. However, I would consider the performance of such a query if your dataset is very large.

If your going to use a Group By or Sub Query to get the data, then make sure both the ID and Parent columns have separate indexes.

To get even better performance, you should add a column called "haschildren" which could be a "bit" datatype. This column should then be updated from your application code when items are Inserted or Deleted. This would allow you to run the much quicker query:

SELECT * FROM table WHERE haschildren IS NOT NULL

Answer 5

The solutions above are fine, but you shouldn't add column like 'haschildren' unless you really have a performance problem (see the post by GateKiller). Such a column denormalizes the database, i.e. the same piece of information will be stored in 2 places, which makes it more likely for your data to become inconsistent. You will have to maintain this column whenever you insert a new child, delete an existing one or update the parent of a child.