Re: [SQLObject] left join with subquery and WHERE

[Imre H. <ble...@gm...>]

On Fri, 2010-05-14 at 19:21 +0400, Oleg Broytman wrote:
> On Fri, May 14, 2010 at 04:43:46PM +0200, Imre Horvath wrote:
> > sqlite> select * from pricegroups 
> >    ...> left join (select * from products_pricegroups
> >    ...> where products_pricegroups.product_id=1) as a 
> >    ...> on (pricegroups.id=a.pricegroup_id);
> 
>    I don't understan what is aliased as 'a'. The subquery? I tried to take
> it into parentheses:
> 
> select * from pricegroups 
>    left join ((select * from products_pricegroups
>    where products_pricegroups.product_id=1) as a)
>    on (pricegroups.id=a.pricegroup_id);
> 
> bug got an error:
> 
>    SQL error near line 1: no such column: a.pricegroup_id.
> 
> Oleg.

Yes, the subquery has to be aliased.

I found a less elegant but working solution:
Select all pricegroups, then select the groups_pricegroups of a product,
and combine the 2 results manually...

Imre