Thread: join with 1 row limit

In mysql this query works fine, what is the equivalent in posgresql?

SELECT *
FROM
products p LEFT JOIN
products_images pi ON p.id = pi.product_id  LIMIT 1
WHERE products.company = 7

postgresql complains about LIMIT

This also fails

SELECT p.*
FROM
products p LEFT JOIN
(SELECT id,server_id,format,product_id FROM products_images pi WHERE pi.product_id = p.id LIMIT 1) pi ON pi.product_id
=p.id 
WHERE p.store_id = 1

with
There is an entry for table "p", but it cannot be referenced from this part of the query.

also this fails

SELECT p.*,
(SELECT id,server_id,format,product_id FROM products_images pi WHERE pi.product_id = p.id LIMIT 1) as AS pi(id, type)
FROM products p
WHERE p.store_id = 1

thanks

Try:

SELECT * FROM products p LEFT JOIN products_images pi ON p.id = pi.product_id WHERE products.company = 7 LIMIT 1;

On Jan 15, 2011, at 8:02 AM, Behringer Behringer wrote:

> SELECT *
> FROM
> products p LEFT JOIN
> products_images pi ON p.id = pi.product_id  LIMIT 1
> WHERE products.company = 7

Thanks & Regards,
Vibhor Kumar

I posted basically the same question last year and did not get a good
answer.

I expect that in the original query below; there are multiple images per
product in product_images and for this query the OP wants only one of
those images per product in the final result.  OP still wants all
products in the final query.

I will use this query to rephrase my question of last year.

How would we modify this JOIN clause to return the smallest (or largest)
image for each product?  In case of tie (multiple images with same size)
we are once again back to the OP's question - how to return only one image?

Frank




On 01/15/11 06:45, Vibhor Kumar wrote:
> Try:
>
> SELECT * FROM products p LEFT JOIN products_images pi ON p.id = pi.product_id WHERE products.company = 7 LIMIT 1;
>
> On Jan 15, 2011, at 8:02 AM, Behringer Behringer wrote:
>
>> SELECT *
>> FROM
>> products p LEFT JOIN
>> products_images pi ON p.id = pi.product_id  LIMIT 1
>> WHERE products.company = 7
>
> Thanks&  Regards,
> Vibhor Kumar
>
>
>
>
>
>
>
>
>
>
>

Behringer Behringer <behringerdj@yahoo.com> writes:
> In mysql this query works fine, what is the equivalent in posgresql?
> SELECT *
> FROM
> products p LEFT JOIN
> products_images pi ON p.id = pi.product_id  LIMIT 1
> WHERE products.company = 7

When asking that sort of question, it would be a good idea to explain
exactly what you expect that syntax to do.  Not all of us know every
nook and cranny of mysql's nonstandard behaviors.

            regards, tom lane

Sorry for that

I just want to get one row from images table for each product row from products table.

I tried using a subquery with ROW() it works but all columns are returned as a record and I don't know how to extract
thedata as separate columns, this is the closest I got 

SELECT
p.*,
(SELECT ROW(id,server_id,format,product_id) FROM products_images pi WHERE pi.product_id = p.id LIMIT 1) AS image

FROM products p

WHERE p.company = 7 ORDER BY id ASC LIMIT 10

--- On Sat, 1/15/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> From: Tom Lane <tgl@sss.pgh.pa.us>
> Subject: Re: [NOVICE] join with 1 row limit
> To: "Behringer Behringer" <behringerdj@yahoo.com>
> Cc: pgsql-novice@postgresql.org
> Date: Saturday, January 15, 2011, 7:49 AM
> Behringer Behringer <behringerdj@yahoo.com>
> writes:
> > In mysql this query works fine, what is the equivalent
> in posgresql?
> > SELECT *
> > FROM
> > products p LEFT JOIN
> > products_images pi ON p.id = pi.product_id  LIMIT
> 1
> > WHERE products.company = 7
>
> When asking that sort of question, it would be a good idea
> to explain
> exactly what you expect that syntax to do.  Not all of
> us know every
> nook and cranny of mysql's nonstandard behaviors.
>
>            
> regards, tom lane
>

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