On Fri, 2010-05-14 at 18:24 +0400, Oleg Broytman wrote: > On Fri, May 14, 2010 at 06:22:05PM +0400, Oleg Broytman wrote: > > On Fri, May 14, 2010 at 03:37:46PM +0200, Imre Horvath wrote: > > > select * from pricegroups > > > left join (select * from products_pricegroups > > > where products_pricegroups.product_id=1) as a > > > on (pricegroups.id=a.pricegroup_id) > > Does the query work at all in MySQL? > > Oleg.
It's in sqlite, but it works. It's also works in postgres. The goal is to select all pricegroups for a product, even if it's not set for the given product. this is my test db: sqlite> .schema CREATE TABLE pricegroups (id integer primary key); CREATE TABLE products (id integer primary key, name text); CREATE TABLE products_pricegroups (id integer primary key, product_id integer, pricegroup_id integer, percent integer); sqlite> select * from products; 1|a 2|b 3|v sqlite> select * from pricegroups; 1 2 3 4 5 6 7 8 sqlite> select * from products_pricegroups; 1|1|1|10 2|1|2|20 3|2|4|20 sqlite> select * from pricegroups ...> left join (select * from products_pricegroups ...> where products_pricegroups.product_id=1) as a ...> on (pricegroups.id=a.pricegroup_id); 1|1|1|1|10 2|2|1|2|20 3|||| 4|||| 5|||| 6|||| 7|||| 8|||| sqlite> select * from pricegroups ...> left join (select * from products_pricegroups ...> where products_pricegroups.product_id=2) as a ...> on (pricegroups.id=a.pricegroup_id); 1|||| 2|||| 3|||| 4|3|2|4|20 5|||| 6|||| 7|||| 8|||| I hope i was clear Regards: Imre Horvath ------------------------------------------------------------------------------ _______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
