charlie charlie - 2 years ago 68
MySQL Question

Using 2 JOINS still display rows in table is rows in join table don't exist

I am running this query and the second join on

m2
is for ordering the results but i want to still show the rows from the
admin
table if the
website_order
row in
m2
doesn't exist

SELECT
a.sequence,
CONCAT(a.forename, ' ', a.surname) AS name,
a.position
FROM
admin a
JOIN
admin_meta m1 on a.sequence = m1.user
JOIN
admin_meta m2 ON a.sequence = m2.user
WHERE
a.disabled = '' AND
m1.field = 'display_on_website' AND
m1.value = '1' AND
m2.field = 'website_order'
ORDER BY CAST(m2.value AS DECIMAL)

Answer Source

use left join:

SELECT 
    a.sequence, 
    CONCAT(a.forename, ' ', a.surname) AS name, 
    a.position 
FROM 
    admin a 
JOIN 
    admin_meta m1 on a.sequence = m1.user 
LEFT JOIN 
    admin_meta m2 ON a.sequence = m2.user AND m2.field = 'website_order'
WHERE 
    a.disabled = '' AND 
    m1.field = 'display_on_website' AND 
    m1.value = '1' 

ORDER BY CAST(m2.value AS DECIMAL)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download