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?
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.
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!
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]