Alex Hakkenberg Alex Hakkenberg - 1 year ago 65
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.


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 Source

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

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 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download