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

SELECT
CMRK.CAR_MARK_NAME,
...
p.email,
(
select min(ci.car_image_id) from car_image ci where ci.car_id = c.car_id
) as 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)


Sean

P.S. I would suggest that the "headshot" image for a given car be stored as a field in the Car table, to save yourself for the extra select.