darcuss Posted November 14, 2008 Share Posted November 14, 2008 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. Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/ Share on other sites More sharing options...
Mchl Posted November 14, 2008 Share Posted November 14, 2008 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 Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-690293 Share on other sites More sharing options...
darcuss Posted November 14, 2008 Author Share Posted November 14, 2008 That's brilliant, thanks very much! Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-690312 Share on other sites More sharing options...
fenway Posted November 17, 2008 Share Posted November 17, 2008 Except that it would be much faster as a LEFT JOIN. Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-692155 Share on other sites More sharing options...
Mchl Posted November 17, 2008 Share Posted November 17, 2008 Show me, show me, show me Pretty please Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-692157 Share on other sites More sharing options...
fenway Posted November 17, 2008 Share Posted November 17, 2008 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 Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-692174 Share on other sites More sharing options...
Mchl Posted November 17, 2008 Share Posted November 17, 2008 Yeah... left/right joins make my brain grind to a halt... Link to comment https://forums.phpfreaks.com/topic/132739-solved-relational-table-how-to-check-id-in-one-column-but-not-the-other/#findComment-692207 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.