CODEWITHSUNDEEP
mysql
charlie
charlie

Reputation: 481

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)

Upvotes: 0

Views: 14

Answers (1)

Jens
Jens

Reputation: 69450

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)

Upvotes: 1

Related Questions