Hi All - I have a query as below, which builds individual records from a series of 4 tables

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
                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

Reply via email to