[SOLVED] Join only 1 element

[feri_soft]

Hi all,

I am trying to do something with symfony, but first i should know how its done in mysql. I have 2 tables:

products and images. Each product can have multiple images. And joining these two tables is very easy. But that way i recieve the images as array which i have to order by the field place (as i dont know how to order them by a field during the join). and then show the 1st. Which is a lot of phpcode for nothing. Another way is that what i am using now. During the loop of products call a simple query for the first image. But that can produce 1000s of queries.

 

So my question is how to join only 1 image (the firs image not by PK but by another field place) to the product. Someone has proposed group by ... but i just dont have the knowledge or the idea on how to do it.

I am opened to suggestions. Thanks!

[Barand]

Question re field "place"

 

Is it a ranking and you always want to show the image with place=1

or is is place as in "Athens, Barcelona, Chicago" and you want the first when sorted?

[feri_soft]

Well its a list with all products and i want to show next to each product the first image for this product given its place not its id. I get to the point of joining the images but how to show only the 1st one by image.place. Thanks!

[Barand]

So there will always be an image with place = 1 for each product?

[feri_soft]

Well yes, but even if not if they are ordered by place the first image will be the one with lowest place value. And if there are no images for the certain product i will just link to a local no-image.gif for example.

Thats all. Remember it has to be done in one query, here is where it gets tricky

[Barand]

 

SELECT p.prodname, i.imagename
FROM product p INNER JOIN image i ON p.prodcode = i.prodcode
INNER JOIN
(SELECT prodcode, MIN(place) as minplace FROM image
GROUP BY prodcode) as X ON i.prodcode=X.prodcode AND i.place = X.minplace

 

 

[feri_soft]

Thanks a lot!