Select one image per record

Hi all,
I have two tables. One stores the rental objects (rental) and the other table stores the rental images (rental_image). When I now select a rental object I just want one image (the primary image) per object. The relevant image should have the lowest Position and as a second argument the lowest ID.

Table rental:
ID (Primary Key)
Position
Reference

Table rental_image:
ID (Primary Key)
Rental_ID (Foreign Key)
Position
Image

My current SQL command goes like this:

SELECT rental.ID, rental.Ref, rental_image.Image FROM rental, rental_image WHERE rental.ID=rental_image.Rental_ID group by rental.id order BY rental_image.pos, rental_image.id, rental.Ref

The result works some how, but not always like expected.
Can someone clear up my code?

Thanks
SelamatJalan

Maybe it’s unclear what I want.

First select statement:

SELECT rental.ID, rental.Ref FROM rental

Second statement:

SELECT rental_image.Image FROM rental_image where rental_image.ID = rental.ID order by rental_image.ID limit 1

To explain it a bit further. I have many images per rental object, but I just want to select the first one, which has the lowest position, and which acts as the main image.

Otherwise I have to do Image-select-statement in a loop, when I pick up the rental objects. And that’s what I want to avoid.

Thanks
SelamatJalan

no, it wasn’t unclear, it’s just moderately difficult sql

SELECT rental.ID
     , rental.Ref 
     , rental_image.Image 
  FROM rental
LEFT OUTER
  JOIN ( SELECT Rental_ID
              , MIN(ID) AS lowest_id
           FROM ( SELECT rental_image.Rental_ID
                       , rental_image.ID
                    FROM ( SELECT Rental_ID
                                , MIN(Position) AS lowest_pos
                             FROM rental_image
                           GROUP
                               BY Rental_ID ) AS m1
                  INNER
                    JOIN rental_image
                      ON rental_image.Rental_ID = m1.Rental_ID
                     AND rental_image.Position = m1.lowest_pos
                ) AS m2
         GROUP
             BY Rental_ID ) AS m3
    ON m3.Rental_ID = rental.ID
LEFT OUTER
  JOIN rental_image
    ON rental_image.Rental_ID = m3.Rental_ID
   AND rental_image.ID = m3.lowest_id

Hi Rudy, thanks for your support. It’s working like charm, but I need to slightly extend the query and I don’t have a glue how to get this managed.

The table rental has a column called active, which indicates whether a rental object is available to the public or not; so I need to extend the query by the condition.

AND rental.Active='1'

But I’m not sure where to put this peace of code to! :frowning:

And the second extension; a table holding the descriptions of each rental object in all available languages called rental_desc where I need the column Claim. This table has the Foreign Key Rental_ID to make the relation to the rental object. To me on a former query it just was a join, like:

AND rental.ID=rental_desc.Rental_ID AND Lng='1'

But after so much derivatives I’m not sure where to put this code also.

Thanks and Merry Christmas to you and your family and a good New Year
SelamatJalan

SELECT rental.ID
     , rental.Ref 
     , [COLOR="Blue"]rental_desc.Claim[/COLOR]
     , rental_image.Image 
  FROM rental
[COLOR="blue"]INNER
  JOIN rental_desc
    ON rental_desc.Rental_ID = rental.ID
   AND rental_desc.Lng = 1[/COLOR]
LEFT OUTER
  JOIN ( SELECT Rental_ID
              , MIN(ID) AS lowest_id
           FROM ( SELECT rental_image.Rental_ID
                       , rental_image.ID
                    FROM ( SELECT Rental_ID
                                , MIN(Position) AS lowest_pos
                             FROM rental_image
                           GROUP
                               BY Rental_ID ) AS m1
                  INNER
                    JOIN rental_image
                      ON rental_image.Rental_ID = m1.Rental_ID
                     AND rental_image.Position = m1.lowest_pos
                ) AS m2
         GROUP
             BY Rental_ID ) AS m3
    ON m3.Rental_ID = rental.ID
LEFT OUTER
  JOIN rental_image
    ON rental_image.Rental_ID = m3.Rental_ID
   AND rental_image.ID = m3.lowest_id
 [COLOR="Red"]WHERE rental.Active = 1[/COLOR]