CODEWITHSUNDEEP
mysqlleft-join
Ivar
Ivar

Reputation: 4392

MySQL: "Ignore" if a table row is missing during JOIN

I'm doing a LEFT JOIN on three tables, where the table "time" doesn't necessarily contain any matching rows. But if no matching rows is found in that table, the linked data disappears.

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;

How can I make this work?

Thank you in advance.

Upvotes: 0

Views: 1089

Answers (3)

georgepsarakis
georgepsarakis

Reputation: 1957

I am not sure if this is the problem here, but the behavior of commas vs JOINs changed after a certain MySQL version. Try this 

 ...
 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
 ...

Also modify the SUM with IFNULL as @ajreal suggests.

Upvotes: 0

Sandeepan Nath
Sandeepan Nath

Reputation: 10284

I think your basic query is correct (with the join under braces)

Just replace

SUM(t.hours) AS time_hours

with 

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

Upvotes: 0

ajreal
ajreal

Reputation: 47311

add

WHERE t.job_id IS NOT NULL before GROUP BY

Try Replace

SUM(t.hours) AS time_hours

to

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

And remove the time from the join

Upvotes: 1

Related Questions