Alex Hakkenberg Alex Hakkenberg - 16 days ago 7
MySQL Question

MySQL left JOIN, result if not all rows are set not working

I got the following query.
This should return a single row but it doesn't.

Unfortunately nothing.

When I remove the last join clause :

LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` = `serene_pagecontent`.`pagecontent_link`


I do get a result. But when added it doesn't.
For as far as I know adding LEFT before join should deliver a result even if one of the fields is missing.

SELECT * FROM `serene_page`
LEFT JOIN `serene_pageoptions` ON `serene_page`.`page_id` = `serene_pageoptions`.`pageoptions_link`
LEFT JOIN `serene_pagetitle` ON `serene_page`.`page_id` = `serene_pagetitle`.`pagetitle_link`
LEFT JOIN `serene_pageorder` ON `serene_page`.`page_id` = `serene_pageorder`.`pageorder_link`
LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` = `serene_pagecontent`.`pagecontent_link`
WHERE `page_id` = 34 AND `pageoptions_language` = 'NL' AND `pagetitle_language` = 'NL' AND `pagecontent_language` = 'NL'


Can anyone tell me what I am missing.

EDIT

WHERE `serene_page`.`page_id` = 34
AND serene_pageoptions`.`pageoptions_language` = 'NL'
AND serene_pagetitle`.`pagetitle_language` = 'NL'
AND serene_pagecontent`.`pagecontent_language` = 'NL'



EDIT SOLUTION : solution by "juergen d"

SELECT * FROM `serene_page`

LEFT JOIN `serene_pageoptions` ON `serene_page`.`page_id` =`serene_pageoptions`.`pageoptions_link` AND
`serene_pageoptions`.`pageoptions_language` = 'NL'
LEFT JOIN `serene_pagetitle` ON `serene_page`.`page_id` = `serene_pagetitle`.`pagetitle_link` AND
`serene_pagetitle`.`pagetitle_language` = 'NL'

LEFT JOIN `serene_pageorder` ON `serene_page`.`page_id` = `serene_pageorder`.`pageorder_link`


LEFT JOIN `serene_pagecontent` ON `serene_pageorder`.`pageorder_id` =
`serene_pagecontent`.`pagecontent_link` AND
`serene_pagecontent`.`pagecontent_language` = 'NL'

WHERE `serene_page`.`page_id` = 34


I wasn't aware of the fact that I had to add the subclause if not
it would turn to normal JOINS. Thanks!!

Answer

If you left join you need to put the conditions of the joined tables into the JOIN. Otherwise the LEFT JOINS turn into INNER JOINs

SELECT * 
FROM `serene_page` s
LEFT JOIN `serene_pageoptions` po ON s.`page_id` = po.`pageoptions_link`
                                 AND po.`pageoptions_language` = 'NL' 
LEFT JOIN `serene_pagetitle` pt ON s.`page_id` = pt.`pagetitle_link`
                               AND pt.`pagetitle_language` = 'NL' 
LEFT JOIN `serene_pageorder` o ON s.`page_id` = o.`pageorder_link`
LEFT JOIN `serene_pagecontent` c ON o.`pageorder_id` = c.`pagecontent_link`
                                AND c.`pagecontent_language` = 'NL'
WHERE s.`page_id` = 34