emredelioglu emredelioglu - 6 months ago 19
SQL Question

finding children of excluding in a parent-child relationship

I have a table X where there is a one-to-many parent-child relationship to itself that is handled by

parent_id
attribute.

| id | parent_id | type |
|----|-----------|------|
| 1 | - | A |
| 2 | - | A |
| 3 | - | A |
| 4 | 1 | B |
| 5 | 1 | C |
| 6 | 2 | B |
| 7 | 3 | C |


I would like to get all rows of type A that don't have any child rows of type B. What is the SQL query to obtain those rows?

Answer
SELECT 
    *
FROM
    x AS t1
WHERE
    t1.type = 'a'
        AND 0 = (SELECT 
            COUNT(*)
        FROM
            x AS t2
        WHERE
            t2.id_parent = t1.id AND t2.type = 'b')