select if match or return the first row-CodePudding

There are two tables. properties and gallery

properties table:
id  |  property
----------------
 1  |  propertyA
 2  |  propertyB


gallery table:
id  |  photo_name  |  property_id  |  is_thumbnail
--------------------------------------------------
 1  |  photo1      |  1            |  0   
 2  |  photo2      |  1            |  1
 3  |  photo3      |  1            |  0   
 4  |  photo4      |  2            |  0
 5  |  photo5      |  2            |  0   
 6  |  photo6      |  2            |  0

I need a MySQL query where the result are properties and for each property a corresponding photo name from the gallery table.

The corresponding photo should be selected by those conditions:

photo which has an attribute is_thumbnail = 1 (this is priority)
OR get a first row from the gallery table matching property_id

So the result of above should look like this:

id  |  photo_name  |  property  
------------------------------
 1  |  photo2      |  propertyA      
 2  |  photo4      |  propertyB

I have tried something like this, but couldn't figure out the last part:

SELECT properties.id AS id, properties.property AS property, 
        gallery.photo_name AS photo_name, gallery.property_id AS property_id, 
        gallery.is_thumbnail AS is_thumbnail 
    LEFT JOIN gallery ON properties.id=gallery.property_id 
WHERE gallery.is_thumbnail=1 OR ?????

CodePudding user response：

You can do it like this:

SELECT properties.id        AS id,
       properties.property  AS property,
       gallery.photo_name   AS photo_name,
       gallery.property_id  AS property_id,
       gallery.is_thumbnail AS is_thumbnail
FROM properties
         LEFT JOIN gallery ON gallery.id = (SELECT id
                                            FROM gallery gallery2
                                            WHERE gallery2.property_id = properties.id
                                            ORDER BY is_thumbnail DESC, id
                                            LIMIT 1)

The idea behind this is to use the is_thumbnail and the id of the gallery to sort:

is_thumbnail in reverse order, so that 1 comes before 0 and then
id in ascending order, so the first photo is returned