[SOLVED] relational table - how to check id in one column but not the other?

[darcuss]

Hi. I'm trying to do something which I'm sure is quite simple but is taking me way longer than I can afford. Help would be very much appreciated.

 

I have a tables of two columns, parent and child, which strore ids and describe a tree structure of items in another table.

 

eg

+--------+-------+

| parent | child |

+--------+-------+

|      1 |    2 |

|      1 |    5 |

|      2 |    3 |

|      2 |    4 |

|      5 |    6 |

|      5 |    7 | 

+--------+-------+

 

How can I query those ids that are a parent but aren't a child? In this simplified example it would be 1 but a far more complex structure will prevail. Thanks again in advance of any help.

[Mchl]

Those that are parent, but are not a child translates as, those parents who do not appear in child column

 

SELECT parent FROM parent_child WHERE parent NOT IN (SELECT child FROM parent_child) GROUP BY parent

[darcuss]

That's brilliant, thanks very much!

[fenway]

Except that it would be much faster as a LEFT JOIN.

[Mchl]

Show me, show me, show me   Pretty please

[fenway]

Show me, show me, show me   Pretty please

 

Like so:

SELECT pc1.parent FROM parent_child AS pc1 LEFT JOIN parent_child AS pc2 ON ( pc2.child = pc1.parent ) WHERE pc2.child IS NULL GROUP BY pc1.parent

[Mchl]

Yeah... left/right joins make my brain grind to a halt...