Basically I need to creat a query that will return the detaileds of the item specified by the itemID and then from the linked table containing photos of the item (indexed by photoID) I need to get one record. This record will essentially be the record linked to itemID with the lowest list order (1 first item in the list). The photos are indexed by photoID and the quety below returns only the highest photoID value which is not the lowest list_order value.If I remove [@item_photo_order = MIN(pp.list_order), ] from the code, then put [MAX(pp.photoID)] and add [ORDER BY pp.list_order DESC] will this work or is this a bad way to go about this?Any help would be great.Thanks,Julian.SELECT @item_photo_order = MIN(pp.list_order), @item_photoID = pp.photoID, @item_photo_ext = pp.file_extension,@item_title = p.title, @item_price = p.price, @item_type = pt.item_typeFROM item pINNER JOIN item_type pt ON p.item_typeID = pt.item_typeIDLEFT JOIN item_photo pp ON p.itemID = pp.itemIDWHERE p.itemID = @itemIDGROUP BY pp.list_order, pp.photoID, pp.file_extension, p.title, p.price, pt.item_type
MINs and MAXs from joined tables
Posted - 2005-06-21 : 08:46:06
