Voortman Voortman - 1 year ago 57
MySQL Question

mysql joining 4 tables return null values

I have data from four tables which I need to join: dimensions, dimensionresults, products and productreports. My goal is to select all dimensions (NULL included) for a report belonging to a product so users can either see or edit the existing values or fill in new values for empty reports.

So far I have tried:

SELECT d.DID, Nominal, r.`Result`, r.`PRID`
FROM `Dimensions` d
JOIN `Products` x ON d.`PID` = x.`PID`
LEFT JOIN `DimensionResults` r ON d.`DID` = r.`DID`
JOIN `ProductReports` p ON r.`PRID` = p.`PRID` AND x.`PID` = p.`PID`
WHERE r.`PRID` = 'PRID17ca-fe04-77ba-6ba8-94416f25197f'


And some variations to that: without the AND on the last join and some other sequences for the JOIN commands but somehow the SQL will not select the not-reported dimensions of a product (i.e. the null values).

I have made a SQL fiddle to further clarify: http://sqlfiddle.com/#!9/42abe7/6

What am I not seeing?

--EDIT:--

With the help of Rahul's answer I figured it out:

SELECT d.DID, Nominal, r.`Result`, r.`PRID`
FROM `Dimensions` d
JOIN `Products` x ON d.`PID` = x.`PID`
LEFT JOIN `DimensionResults` r ON d.`DID` = r.`DID`
AND r.`PRID` = 'PRID17ca-fe04-77ba-6ba8-94416f25197f'
LEFT JOIN `ProductReports` p ON r.`PRID` = p.`PRID` AND x.`PID` = p.`PID`
WHERE d.`PID` = 'PID107ca-fe04-77ba-6ba8-94416f25197f'


Gives the result I'm looking for. I thought it would be able to do without also passing the PID parameter but at least this works.

Thanks for the quick responses!

Answer Source

Move that WHERE condition (WHERE r.PRID = 'PRID17ca-fe04-77ba-6ba8-94416f25197f') to JOIN ON condition like below. Check your modified fiddle http://sqlfiddle.com/#!9/42abe7/14

LEFT JOIN `DimensionResults` r ON d.`DID` = r.`DID`
AND r.`PRID` = 'PRID17ca-fe04-77ba-6ba8-94416f25197f'