MySQL complex JOIN problem - Unable to achieve proper results

Question

I have two table: Vehicles(Id, VIN) and Images(Id, VehicleId, Name, Default). I need to select the vehicles VIN and its default picture to display in a table. The problem I am having is that if a default picture is not set I still would like to select an image to display if it exists. If no images exist the vehicle information obviously must also still display. Here is what I have so far:

SELECT
    Vehicles.Id, Vehicles.VIN, Images.Name AS Image,
    (SELECT COUNT(*) FROM Images WHERE VehicleId = Vehicles.Id) AS ImageCount
FROM
    Vehicles
LEFT JOIN
    Images ON Images.VehicleId = Vehicles.Id
WHERE
    Images.Default = 1

This statement will display vehicles which have a default image but will not display anything if no default is set. What do I need to change?

EDIT*

To describe my problem better here is some test data:

VEHICLES:
ID VIN
1  12341234123412341
2  23452345234523452
3  34534534534534534

IMAGES:
ID VEHICLEID NAME DEFAULT
1  1         a    1
2  1         b    0
3  2         c    0
4  2         d    0

Even though vehicle 2 has no default I want it to select an image to display. Also vehicle 3 has no images at all but I still need it to show up in the table with no image. Vehicle 1 will display its default image because it is set. I hope this clears things up.

Answer 1

WHERE Images.Default = 1 OR Images.Default IS NULL

The where clause is turning the left join into an inner join, since it requires a matching record to satisfy the criteria.

Answer 2

I would write this query this way:

SELECT v.Id, v.VIN, 
  GROUP_CONCAT(IF(i.Default=1, i.Name, NULL)) AS ImageName,
  COUNT(i.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i ON (i.VehicleId = v.Id AND i.Default = 1)
GROUP BY v.Id;

---

I understand that you want an image to display even if there are none marked as the default. Here's a solution that does this:

SELECT v.Id, v.VIN, 
  COALESCE(i1.Name, MIN(i2.Name)) AS ImageName,
  COUNT(i2.Id) AS ImageCount
FROM Vehicles v
LEFT JOIN Images i1 ON (i1.VehicleId = v.Id AND i1.Default = 1)
LEFT JOIN Images i2 ON (i2.VehicleId = v.Id)
GROUP BY v.Id;

You didn't specify which image it should display if none are default. So I arbitrarily designed the expression to show the image name that is first alphabetically.

Also note that there's a Cartesian product between i1 and i2 in this query. I would assume that you don't mark more than one one default image for a given vehicle, but if you do, you'll get unexpected results, e.g. ImageCount will be multiplied by the number of default images. This isn't a problem if there's at most one default image per vehicle.

Answer 3

I'd do something like this to get the default name on ones with no image.

SELECT
    Vehicles.Id, Vehicles.VIN, COALESCE(Images.Name,'default image name')AS Image,
    (SELECT COUNT(*) FROM Images WHERE VehicleId = Vehicles.Id) AS ImageCount
FROM
    Vehicles
LEFT JOIN
    Images ON Images.VehicleId = Vehicles.Id
WHERE
     Images.Default = 1 OR Images.Name IS NULL

Answer 4

SELECT
    Vehicles.Id, Vehicles.VIN, Images.Name AS Image,
    (SELECT COUNT(*) FROM Images WHERE VehicleId = Vehicles.Id) AS ImageCount
FROM
    Vehicles
LEFT JOIN
    Images ON Images.VehicleId = Vehicles.Id
WHERE
    Images.Default = 1 OR Images.Name IS NULL

This assumes that all Images have a Name. The problem with your original query is that requiring "Images.Default = 1" implies that the query has to have matched an Image. Allowing Images.Id to be NULL will also catch the case where no image was matched and the left join filled in null values.

Answer 5

OK, here's the setup:

mysql> create table VEHICLES ( ID INT PRIMARY KEY, VIN CHAR( 17 ) );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 1, '12341234123412341' );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 2, '23452345234523452' );
mysql> INSERT INTO VEHICLES ( ID, VIN ) VALUES( 3, '34534534534534534' );

Note I had to rename the column DEFAULT to DEF in the IMAGES table:

mysql> CREATE TABLE IMAGES ( ID INT PRIMARY KEY, VEHICLEID INT, NAME VARCHAR(20), DEF INT );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 1, 1, 'a', 1 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 2, 1, 'b', 0 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 3, 2, 'c', 0 );
mysql> INSERT INTO IMAGES( ID, VEHICLEID, NAME, DEF ) VALUES( 4, 2, 'd', 0 );

And here's the solution.

First we need a query that gets one image row per vehicle, choosing the default one if there is one.

We do this by sorting the images in descending order of DEF (so the 1s are at the top), and then grouping by VEHICLEID to make sure there is only one row per vehicle.

mysql> SELECT * FROM ( SELECT * FROM IMAGES ORDER BY DEF DESC ) sortedimages GROUP BY VEHICLEID;
+----+-----------+------+------+
| ID | VEHICLEID | NAME | DEF  |
+----+-----------+------+------+
|  1 |         1 | a    |    1 | 
|  3 |         2 | c    |    0 | 
+----+-----------+------+------+

Now we select from the VEHICLES table, and LEFT OUTER JOIN to the above query to make sure we always get one row per vehicle:

mysql> SELECT * FROM VEHICLES LEFT OUTER JOIN ( SELECT * FROM ( SELECT * FROM IMAGES ORDER BY DEF DESC ) sortedimages GROUP BY VEHICLEID ) defaultimages ON VEHICLES.ID = defaultimages.VEHICLEID;
+----+-------------------+------+-----------+------+------+
| ID | VIN               | ID   | VEHICLEID | NAME | DEF  |
+----+-------------------+------+-----------+------+------+
|  1 | 12341234123412341 |    1 |         1 | a    |    1 | 
|  2 | 23452345234523452 |    3 |         2 | c    |    0 | 
|  3 | 34534534534534534 | NULL |      NULL | NULL | NULL | 
+----+-------------------+------+-----------+------+------+