mysql - 使用 2 JOINS 仍然显示表中的行，因为连接表中的行不存在

Question

我正在运行此查询，m2 上的第二个连接用于对结果进行排序，但如果 m2 中的 website_order 行不存在

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 1

使用左连接:

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)