trying to put a sub-clause into a query

steamPunk

here's my prob :

I have 2 tables "properties" and "images"

the images table contains all the images for each property, linked via a 'prop_id'

at the moment I'm selecting all the properties and just 1 image for each property using this query :

 	$sql = "SELECT DISTINCT(t1.prop_id) AS pid, t2.image_thumb,t1.prop_name,t3.loc_type_name   

  FROM properties AS t1  

  LEFT JOIN images AS t2 ON t2.prop_id=t1.prop_id
  LEFT JOIN location_types AS t3 ON t3.loc_type_id=t1.loc_type_id
  WHERE t1.prop_online='1' 
  GROUP BY pid    

  ORDER BY t1.prop_name ASC";

this works fine but my problem is that the images are ordered using a field called "image_order" and I'd like the first image in the order (with an "image_order" value of 1) to be selected by this query

but what's happening is that the query above is selecting the image with the lowest "image_id" (the first image uploaded) and NOT the first image in the ordering

So ... what I'd like to do is to include a bit in the query that says "select the lowest image_order value" kind of like this pseudo-code :

 	$sql = "SELECT 
DISTINCT(t1.prop_id) AS pid, 
(t2.image_thumb AS img_th WHERE image_order = MIN() && prop_id=t1.prop_id),
t1.prop_name,
t3.loc_type_name   

  FROM properties AS t1  

  LEFT JOIN images AS t2 ON t2.prop_id=t1.prop_id
  LEFT JOIN location_types AS t3 ON t3.loc_type_id=t1.loc_type_id
  WHERE t1.prop_online='1' 
  GROUP BY pid    

  ORDER BY t1.prop_name ASC"

do you think it's possible ? and if so, how could I do it ??

thanks for any help you can give

steamPunk

actually the answer is dead simple but i wasn't seeing the wood for the trees

I just needed to add a where condition : " ... AND t1.image_order<2 ..."

 	$sql = "SELECT DISTINCT(t1.prop_id) AS pid, t1.image_thumb,t2.prop_name,t3.loc_type_name FROM images AS t1  

  LEFT JOIN properties AS t2 ON t2.prop_id=t1.prop_id
  LEFT JOIN location_types AS t3 ON t3.loc_type_id=t2.loc_type_id
  WHERE prop_online='1' 
  AND t1.image_order<2 
  GROUP BY pid    

  ORDER BY t2.prop_name ASC";

this does assume that there's always an image with an image_order of 1 or 0, but it seems to work ok