I have a query where I'm joining 2 tables with a LEFT JOIN. To simplify the example, let's say it's fetching cars and their options. There are 31 possible options and the output should include all options that were potentially available.
SELECT
car.instance_id,
options.option_id,
options.option_name,
car.value
FROM options
LEFT JOIN car ON car.option_id = options.option_id AND car.instance_id = 3
WHERE options.version_id = 1
The issue here is that for every option that is not in the car, the car.instance_id returns NULL. That's fine for just dumping the data and working with it, not fine if the query is used in for a reporting data object, where I need the car.instance_id for those missing options.
Is there a way to get the car.instance_id to be in that column when there is no match on the option_id's in the JOIN? I'm restricting this one to car.instance = 3, but for the report processor, there will be a range and grouping.
Table Structure – Options:
+------------+-----------+------------------------------+
| version_id | option_id | option_name |
+------------+-----------+------------------------------+
| 1 | 1 | powerWindows |
| 1 | 2 | powerDoorLocks |
| 1 | 3 | climateControl.automatic |
| 1 | 4 | climateControl.dualZone |
| 1 | 5 | automaticTransmission |
| 1 | 6 | automaticTransmission.7Speed |
| 1 | 7 | slidingSideDoors |
| 1 | 8 | slidingSideDoors.power |
| 1 | 9 | 6wayDriverSeat |
| 1 | 10 | heatedSeats |
| 1 | 11 | heatedMirrors |
| 1 | 12 | airBags |
| 1 | 13 | airBags.passenger |
| 1 | 14 | 20inchWheels |
| 1 | 15 | kitchenSink |
| 1 | 16 | powerDoorLocks |
| 1 | 17 | steeringWheel |
| 1 | 18 | plushCarpet |
| 1 | 19 | allWeatherFloorMats |
| 1 | 20 | seatBelts |
| 1 | 21 | v24Engine |
| 1 | 22 | mechanicalFuelInjection |
| 1 | 23 | rearSpoiler |
| 1 | 24 | rearSpoiler.wing |
| 1 | 25 | racingStripes |
| 1 | 26 | tintedWindows |
| 1 | 27 | tintedMirrors |
| 1 | 28 | keylessEntry |
| 1 | 29 | keylessEntry.keyful |
| 1 | 30 | comfortPackage |
| 1 | 31 | unComfortPackage |
+------------+-----------+------------------------------+
Table Structure – Cars:
+----+-------------+-----------+-------+
| id | instance_id | option_id | value |
+----+-------------+-----------+-------+
| 1 | 3 | 1 | |
| 2 | 3 | 2 | |
| 3 | 3 | 3 | |
| 4 | 3 | 4 | |
| 5 | 3 | 5 | |
| 6 | 3 | 7 | |
| 7 | 3 | 9 | |
| 8 | 3 | 14 | |
| 9 | 3 | 15 | |
| 10 | 3 | 16 | |
| 11 | 3 | 17 | |
| 12 | 3 | 18 | |
| 13 | 3 | 20 | |
| 14 | 3 | 21 | |
| 15 | 3 | 22 | |
| 16 | 3 | 28 | |
| 17 | 3 | 29 | |
| 18 | 3 | 31 | |
+----+-------------+-----------+-------+
Inverting the table order doesn't work because you'd have to switch to a RIGHT JOIN because you'd still need the full option list.
I'd like to avoid using a function (stored procedure) as a source for a reporting tool.
Return set:
+-------------+-----------+------------------------------+-------+
| instance_id | option_id | option_name | value |
+-------------+-----------+------------------------------+-------+
| 3 | 1 | powerWindows | |
| 3 | 2 | powerDoorLocks | |
| 3 | 3 | climateControl.automatic | |
| 3 | 4 | climateControl.dualZone | |
| 3 | 5 | automaticTransmission | |
| 3 | 7 | slidingSideDoors | |
| 3 | 9 | 6wayDriverSeat | |
| 3 | 14 | 20inchWheels | |
| 3 | 15 | kitchenSink | |
| 3 | 16 | powerDoorLocks | |
| 3 | 17 | steeringWheel | |
| 3 | 18 | plushCarpet | |
| 3 | 20 | seatBelts | |
| 3 | 21 | v24Engine | |
| 3 | 22 | mechanicalFuelInjection | |
| 3 | 28 | keylessEntry | |
| 3 | 29 | keylessEntry.keyful | |
| 3 | 31 | unComfortPackage | |
| NULL | 25 | racingStripes | |
| NULL | 26 | tintedWindows | |
| NULL | 27 | tintedMirrors | |
| NULL | 11 | heatedMirrors | |
| NULL | 12 | airBags | |
| NULL | 10 | heatedSeats | |
| NULL | 13 | airBags.passenger | |
| NULL | 19 | allWeatherFloorMats | |
| NULL | 24 | rearSpoiler.wing | |
| NULL | 8 | slidingSideDoors.power | |
| NULL | 30 | comfortPackage | |
| NULL | 6 | automaticTransmission.7Speed | |
| NULL | 23 | rearSpoiler | |
+-------------+-----------+------------------------------+-------+
It's those nulls at the end I'd like to have with the instance ID.
Best Answer
You have to change the order you join the tables
Joining
carsto theoptionsmeans "list all possible options and show the cars having them".When you join
optionsto thecarsthe resulting table have the different meaning: "list all the cars and show all the options they have".NULLs in my query means "that particular car has no available options" while for your query NULLs means "no cars having that particular option".
If you want to eliminate any NULLs from output you have to add restriction
AND options.option_id IS NOT NULLto theWHEREclause.