Обсуждение: 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