1

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.

  • 2
    Where are the options of car stored? In car or in options? It seems that car.option_id is storing a single option for a car. How do you store multiple options? I suggest you add sample data and wanted output. – ypercubeᵀᴹ Jul 6 '17 at 20:28
  • Hi ypercube. Yes, car has the options in it. Car is 1 or more options. Options is all 31 possible. I'll make some sample data since Kondybas solution below only returned the 18 matching options. – DOlson Jul 6 '17 at 21:21
  • So SELECT * FROM options WHERE options.version_id = 1; will give the 31 rows (possible options) and SELECT * FROM car WHERE car.instance_id = 3; will give you the 18 options of a specific car? – ypercubeᵀᴹ Jul 6 '17 at 21:27
  • Yes, that's right. – DOlson Jul 6 '17 at 21:42
  • So, coming back to this I needed to post the return in order to show my issue.. – DOlson Jul 10 '17 at 18:35
2

You have to change the order you join the tables

SELECT car.instance_id
     , options.option_id
     , options.option_name
     , car.value
 FROM car
 LEFT JOIN options ON options.option_id = car.option_id 
WHERE options.version_id = 1
  AND car.instance_id = 3;

Joining cars to the options means "list all possible options and show the cars having them".

When you join options to the cars the 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 NULL to the WHERE clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.