SELECT menuitems.tour_code, image_caption, image_code,
region, country, product_type
FROM tourcountries, tour_product, countries, menuitems
LEFT OUTER JOIN image_ref
ON image_ref.tour_code=menuitems.tour_code
WHERE
FIND_IN_SET('holiday',menuitems.types)>0
AND menuitems.tour_code=tour_product.product_code
AND tour_product.id=tourcountries.holiday_id
AND tourcountries.country_id=countries.country_id
AND countries.country_id=1
GROUP BY tour_code";This works as desired, returning one correctly formed result row with ancilliary information (caption, image, region etc), for each tour_product ID in my table. However I am unable to see a way to pick the minimum image_code value from the image_ref table.
The LEFT OUTER JOINclause is necessary, because the table image_ref on which I do a contains between zero and many matching rows. I need to return only one row containing a product and a single image code, so I then use GROUP BY tour_code to reduce the rows to one.
For this query, I wish to return the *minimum* value of image_code, and return all this as one row.
Usually what I get back though is the last image_code, as a result of the GROUP BY clause
As you might imagine, this is quite tricky but I'm trying to determine if it's impossible using a single query : What I intend to happen, is ORDER BY image_code ASC , *before* grouping (I know I can't do that !), so that the returned row contains the earliest possible image_code.
Any suggestions if there are avenues I might explore here > ?
Cheers all - Neil Smith.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
