Skip site navigation (1) Skip section navigation (2)

Re: join with 1 row limit

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: join with 1 row limit
Date: 2011-01-16 11:17:23
Message-ID: iguk43$m38$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Josh KupershmidtDate: 2011-01-16 17:15:49
Subject: Re: async fast-path calls?
Previous:From: Behringer BehringerDate: 2011-01-15 18:37:54
Subject: Re: join with 1 row limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group