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

select, sql

Solution

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

Problem

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?

Original source