Through-the-looking-glass MySQL JOIN query - how to flip it round the right way?

[bothwell]

I have a join query! It works great, only back-to-front.

 

I've got a pages table which has some ids in it, and a children table with some other ids in it. If a record is NOT a child of an existing page, and it also isn't set as a menu item (the pages.menu = '0' bit), I want it to display in a list.

 

mysql_query("SELECT pages.id,pages.name,pages.menu,children.childid FROM pages INNER JOIN children ON children.childid = pages.id WHERE pages.menu = '0' AND children.childid <> '0'");

 

The php output displays any record that IS a child of an existing page that isn't set as a menu item, which is the wrong way round. Changing the query to children.childid = '0' leads to nothing being displayed at all regardless.

 

I should say that pages.menu is boolean, so it can only be 1 or 0, but childid can be any number. Where am I going wrong here? I'm pretty new to joins.

[Barand]

Sounds like you need a LEFT JOIN

 

http://www.phpfreaks.com/tutorial/data-joins-unions

[bothwell]

Ooh, almost! On the right track, though. Just using a left join showed me all the pages that weren't set as menu items, but didn't care whether there was a corresponding entry in children - I needed to only show those results that returned NULL. The way to do that was adding an IS NULL (doh) statement at the end of my WHERE clause:

 

mysql_query("SELECT pages.id,pages.name,pages.menu,children.childid FROM pages LEFT JOIN children ON children.childid = pages.id WHERE pages.menu = '0' AND children.childid IS NULL");

 

Working now, thanks for pointing me onto the right path!