SQL Problem

[princeads]

Hi guys,

 

Could anyone help with this problem - I have a MYSQL database that uses PHP to create a dynamic website. The menu is built from the database in a table called cmsmenus, with a table called cmspages that uses the cmsmenu id as a foriegn key. I've attached my sql statement below:

 

$sql = "SELECT m.id, m.label, m.link_url, m.parent_id, m.topmenu, m.leftmenu FROM cmsmenu m LEFT JOIN cmspages p ON p.menu_ID = m.id AND p.statusID = 1 ORDER BY m.parent_id, m.orderid ASC";

 

Hopefully this helps explains the scenario, at the moment all results in the cmsmenu table are retrieved which is great, but the "AND p.statusID = 1" doesn't seem to be working. Even if the corrsponding row in cmspages has value 2 the query still retrieves it, which ideally I don't want!

 

Any help on this would be great. cheers

AB

[peranha]

Try putting the 1 in ''

 

$sql = "SELECT m.id, m.label, m.link_url, m.parent_id, m.topmenu, m.leftmenu FROM cmsmenu m LEFT JOIN cmspages p ON p.menu_ID = m.id AND p.statusID = '1' ORDER BY m.parent_id, m.orderid ASC";

 

See what that gives you.

[princeads]

Thanks for the reply but that didn'nt seem to work.

 

I think I might have the wrong type of join but not sure.

 

Cheers

AB

[peranha]

Yeah, I am not sure, I am not strong in SQL, but you can try INNER JOIN to see if that works.

[princeads]

Thanks I tried that aswell but doesn't seem to work.

 

It's annoying because I've tried most of the joins but not being an expert I'm struggling.

 

Cheers anyway.

AB

[princeads]

Anyone got any tips??

 

Chees guys

AB

[richardw]

Try the join with a WHERE clause for status and also keep the 1 in '' as mentioned earlier

 

$sql = "SELECT m.id, m.label, m.link_url, m.parent_id, m.topmenu, m.leftmenu FROM cmsmenu m LEFT JOIN cmspages p ON p.menu_ID = m.id  WHERE p.statusID = '1' ORDER BY m.parent_id, m.orderid ASC";