feri_soft Posted December 2, 2007 Share Posted December 2, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/ Share on other sites More sharing options...
Barand Posted December 2, 2007 Share Posted December 2, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-404187 Share on other sites More sharing options...
feri_soft Posted December 3, 2007 Author Share Posted December 3, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-405385 Share on other sites More sharing options...
Barand Posted December 3, 2007 Share Posted December 3, 2007 So there will always be an image with place = 1 for each product? Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-405391 Share on other sites More sharing options...
feri_soft Posted December 4, 2007 Author Share Posted December 4, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-406378 Share on other sites More sharing options...
Barand Posted December 4, 2007 Share Posted December 4, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-406408 Share on other sites More sharing options...
feri_soft Posted December 5, 2007 Author Share Posted December 5, 2007 Thanks a lot! Quote Link to comment https://forums.phpfreaks.com/topic/79791-solved-join-only-1-element/#findComment-407023 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.