Voortman - 1 year ago 57

MySQL Question

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