Re: join with 1 row limit

On 2011-01-15, Behringer Behringer <behringerdj@yahoo.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