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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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