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

join with 1 row limit

From: Behringer Behringer <behringerdj(at)yahoo(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: join with 1 row limit
Date: 2011-01-15 02:32:14
Message-ID: 370933.72551.qm@web45205.mail.sp1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



      

Responses

pgsql-novice by date

Next:From: Vibhor KumarDate: 2011-01-15 11:45:50
Subject: Re: join with 1 row limit
Previous:From: Jasen BettsDate: 2011-01-15 00:41:02
Subject: Re: Collation and Case Insensitivity

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