Getting only the default image for a property

Question

I am trying to retrieve a list of properties and to do this I have to tie together 4 tables. The tables are: property, property_type, state, and photo.

The problem is getting ONLY the default image for a property. The photo with the lowest rank number should be the default. So if there are 2 photos for property 10, than the one with the lower rank value should be in this result set.

Take a look at what I have so far, it should explain what I have done so far. Basically it shows a list of properties including a photo id assigned to that property. Unfortunately since I am using GROUP BY to eliminate extra results (dont need 5 rows for one property if there are 5 photos assigned to it), ordering by photo rank doesnt change my results as I thought it would.

SELECT 
 property.property_id,
 property.property_name,
 state.state_id,
 state.state_name,
 photo.photo_id,
 photo.rank
FROM property 
JOIN photo
 ON property.property_id = photo.property_id
JOIN property_type
 ON property.property_type_id = property_type.property_type_id
JOIN state
 ON property.state_id = state.state_id    
GROUP BY property.property_id
ORDER BY  
 state.state_name ASC,
 property.property_name ASC,
 photo.rank ASC

Can anyone point me in the right direction? One thing to note, I am coming into this project which is already completed. So database structure cannot be changed at this point.

Answer 1

Use:

  SELECT p.property_id,
         p.property_name,
         s.state_id,
         s.state_name,
         x.photo_id,
         x.rank
    FROM PROPERTY p
    JOIN PHOTO x ON x.property_id = p.property_id
    JOIN (SELECT ph.property_id,
                 MIN(ph.rank) AS min_rank
            FROM PHOTO ph
        GROUP BY ph.property_id) y ON y.property_id = x.property_id
                                  AND y.min_rank = x.rank
    JOIN PROPERTY_TYPE pt ON pt.property_type_id = p.property_type_id
    JOIN STATE s ON s.state_id = p.state_id   
GROUP BY p.property_id
ORDER BY s.state_name, p.property_name

I altered your query to do a self-join to a copy of the PHOTO table, to get the minimum rank per property_id value. By joining back to the PHOTO table, I can ensure that only the lowest ranked PHOTO record is returned.