MySQL：“忽略”如果 JOIN 期间缺少表行

Question

我正在对三个表执行 LEFT JOIN，其中表“时间”不一定包含任何匹配的行。但是，如果在该表中找不到匹配的行，则链接的数据就会消失。

SELECT
    w.date AS worker_date,
    w.name AS worker_name,
    w.address AS worker_address,
    w.zip AS worker_zip,
    w.place AS worker_place,
    w.phone AS worker_phone,
    w.email AS worker_email,
    w.company AS worker_company,
    w.accessibility AS worker_accessibility,
    c.date AS client_date,
    c.name AS client_name,
    c.address AS client_address,
    c.zip AS client_zip,
    c.place AS client_place,
    c.phone AS client_phone,
    c.email AS client_email,
    c.web AS client_web,
    c.contact AS client_contact,
    j.date AS job_date,
    j.client_id,
    j.worker_id,
    j.name AS job_name,
    j.description AS job_description,
    j.type AS job_type,
    j.status AS job_status,
    j.proof AS job_proof,
    j.deadline AS job_deadline,
    j.price AS job_price,
    j.final_client AS job_final_client,
    SUM(t.hours) AS time_hours
FROM
    jobs AS j
LEFT JOIN (
    workers AS w,
    clients AS c,
    time AS t
) ON (
    w.id = j.worker_id AND
    c.id = j.client_id AND
    j.id = t.job_id
) GROUP BY
    j.id;

我怎样才能做到这一点？

先感谢您。

Answer 1

在 GROUP BY 之前添加

WHERE t.job_id IS NOT NULL

尝试替换

SUM(t.hours) AS time_hours

到

(SELECT IFNULL(SUM(t.hours),0) FROM time WHERE time.job_id=j.job_id) AS time_hours

并从连接中删除时间

Answer 2

我认为您的基本查询是正确的（大括号下的连接）

只需替换

SUM(t.hours) AS time_hours

为

SUM(if(t.hours is NULL,0,t.hours)) AS time_hours

Answer 3

我不确定这是否是这里的问题，但是逗号与 JOIN 的行为在某个 MySQL 版本之后发生了变化。试试这个

 ...
 FROM jobs AS j LEFT JOIN workers AS w ON w.id = j.worker_id
 LEFT JOIN clients AS c c.id = j.client_id
 LEFT JOIN `time` AS t ON j.id = t.job_id
 ... 

还按照 @ajreal 的建议用 IFNULL 修改 SUM 。