Voortman Voortman - 3 months ago 6
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

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'
Comments