MS SQL: How to separate out records which have no childs in the same table?

Question

Hi everyone!

I have a tricky problem. I have a very complex view in MS SQL 2005 with the following result:

|  ID  |   Name   |  ParentID  |
--------------------------------
|  1   |   Cars   |     1      |
|  2   |   Audi   |     1      |
|  3   |  Toyota  |     1      |
|  4   |  Trucks  |     4      |

Now I want my view to recognize that the record with ID 4 has no children and, because of this, to separate it out.

Any ideas? ;)

Thanks
Torben

Answer 1

WHERE ParentID NOT IN (SELECT     ParentID 
                 FROM       TABLE 
                 WHERE      ParentID <> ID)

Answer 2

Since ParentID = ID for the Parent rows, you want to find the ones where there's only one instance of a given ParentID:

SELECT
   ParentID
FROM
   myTable
GROUP BY
   ParentID
HAVING
   COUNT(1) = 1

Answer 3

These would would be the ones which have no children:

SELECT a.*
FROM theView a
    LEFT JOIN theView b ON (a.a = b.ParentId AND b.Id <> b.ParentId)
WHERE b.Id IS NULL

However ID 2 and ID 3 are without children as well.

Answer 4

SELECT *
FROM Table as parent
WHERE EXISTS (
  SELECT child.ParentID 
  FROM   Table as child
  WHERE  parent.ParentId = child.id
    and parent.id != child.id
)

If the rows without parent aways reference itself, it's easy:

SELECT *
FROM Table as parent
WHERE parent.parentId != parent.id