Return result set even when a join is null

[GregL83]

Hello,

 

I am trying to return a mysql result with multiple table joins...  I need to return these results even if one of the table join returns null...  Is this possible???

 

here is my query:

SELECT sc.name AS name, sc.slug AS slug, ct.name AS city, st.name AS state, sp.picture_id FROM scenes sc JOIN cities ct ON sc.city_id = ct.city_id JOIN states st ON sc.state_id = st.state_id JOIN (SELECT scene_id, picture_id FROM scene_pictures ORDER BY picture_id DESC LIMIT 1) sp ON sc.id = sp.scene_id ORDER BY sc.id DESC LIMIT 5

 

the table scene_pictures sometimes DOES NOT have any pictures, but I would still like to return the rest of the fields...

 

Any help???

 

Thanks in advance

 

[JonnoTheDev]

Use a LEFT JOIN rather than a straight JOIN as this will only return matching records.

[GregL83]

ohhhh... ooopps...   thanks for the help