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.
caror inoptions? It seems thatcar.option_idis storing a single option for a car. How do you store multiple options? I suggest you add sample data and wanted output.SELECT * FROM options WHERE options.version_id = 1;will give the 31 rows (possible options) andSELECT * FROM car WHERE car.instance_id = 3;will give you the 18 options of a specific car?