select Parent Child Rows in same Table

I have table structure like below

id |parent|value
1  | 0    | a |
2  | 1    | b |
3  | 4    | c |
4  | 0    | d |
5  | 0    | e |

I want to display only rows that have a relation parent child

like:

id |parent|value
1  | 0    | a |
2  | 1    | b |
3  | 4    | c |
4  | 0    | d |

every child should have a parent
every parent should have at least one child

This is my code but it does not work properly:

select a.id, a.parent,a.value
from myTable a inner join myTable b 
on a.id = b.parent
union 
select b.id, b.child,b.value
from myTable a inner join myTable b
on a.id = b.parent;

Answer

SELECT
  *
FROM
  yourTable
WHERE
     parent != 0
  OR EXISTS (SELECT *
               FROM yourTable children
              WHERE chilren.parent = yourTable.id
            )

The first condition checks if the row points to a parent, and the second condition checks if the row has any children.

Advertisement

Answer