MySQL complex query not yielding 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.

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

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
    1

should do the trick, as you were limiting by the fact that default was set to 1. I think this answers your question, but may need to be refined for the actual result you want- (are you looking to get the name of the image, or what?)

Answer 2

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 | 
+----+-------------------+------+-----------+------+------+