what is the best way to do a join to a "top 1 row" select of second table? For example CAR and CAR_IMAGES to display just one image. I wrote following select, this works. However I think there must be a clever way to use "ROWS 1" instead of "min() and group by".
SELECT CMRK.CAR_MARK_NAME, CMDL.CAR_MODEL_NAME, fltp.fuel_type_name, C.MANUF_YEAR, C.KILOMETER, C.PRICE, C.CURRENCY_CODE, c.CAR_INFO, clr.COLOR_NAME, p.name, p.phone, p.email, ci.car_image_id FROM CAR c join CAR_MARK cmrk on (C.CAR_MARK_ID=CMRK.CAR_MARK_ID) join CAR_MODEL cmdl on (C.CAR_MODEL_ID=CMDL.CAR_MODEL_ID) join party p on (c.party_id=p.party_id) left join fuel_type fltp on (c.fuel_type_id=fltp.fuel_type_id) left join color clr on (c.color_id=clr.color_id) left join (select car_id, min(car_image_id) as car_image_id from car_image group by car_id) ci on (c.car_id=ci.car_id) where c.car_id=:car_id
