Re: join with 1 row limit

On 2011-01-15, Behringer Behringer <behringerdj(at)yahoo(dot)com> wrote:
> Sorry for that
>
>> > SELECT *
>> > FROM
>> > products p LEFT JOIN
>> > products_images pi ON p.id = pi.product_id  LIMIT
>> 1
>> > WHERE products.company = 7
>
> I just want to get one row from images table for each product row from products table.

SELECT distinct on (p.id)
*
FROM products p
LEFT JOIN products_images pi
ON p.id = pi.product_id WHERE products.company = 7

or perhaps:

SELECT *
FROM products p LEFT JOIN
( SELECT * FRPM products_images pi where p.id = pi.product_id limit
1) as foo ON TRUE
WHERE products.company = 7

an