your
select is:
select * from drink where drink.drink_key not in (
select drink_key from ingredients_alcohol where alcohol_key not in (
select alcohol_key from alcohol where alcohol_type_key in (
select alcohol_type_key from alcohol where alcohol_key in (3, 2, 5)
)
) union select drink_key from ingredients_mixer where mixer_key not in (
select mixer_key from mixer where mixer_type_key in (
select mixer_type_key from mixer where mixer_key in (2, 4, 6)
)
) union select drink_key from ingredients_garnish where garnish_key not in
(1, 2, 5))
There is no union in web2py but you can do:
db(~db.drink.drink_key.belongs("""
select drink_key from ingredients_alcohol where alcohol_key not in (
select alcohol_key from alcohol where alcohol_type_key in (
select alcohol_type_key from alcohol where alcohol_key in (3, 2, 5)
)
) union select drink_key from ingredients_mixer where mixer_key not in (
select mixer_key from mixer where mixer_type_key in (
select mixer_type_key from mixer where mixer_key in (2, 4, 6)
)
) union select drink_key from ingredients_garnish where garnish_key not in
(1, 2, 5)""")).select()
The union could be done at the web2py level but you would have to break tho
into 4 selects.
Anyway, union selects are slow. You may want to consider using a single
table "ingredients" and have a type.
On Monday, 11 June 2012 12:49:50 UTC-5, Amber Doctor wrote:
>
> I'm trying to translate the below sql statement into web2py but I'm not
> sure how to handle the union. Maybe I'm just not seeing the web2py union
> command that would make translating this simple.
>
> Anyone have any suggestions?
>
> select * from drink where drink.drink_key not in (select drink_key from
> ingredients_alcohol where alcohol_key not in (select alcohol_key from
> alcohol where alcohol_type_key in (select alcohol_type_key from alcohol
> where alcohol_key in (3, 2, 5))) union select drink_key from
> ingredients_mixer where mixer_key not in (select mixer_key from mixer where
> mixer_type_key in (select mixer_type_key from mixer where mixer_key in (2,
> 4, 6))) union select drink_key from ingredients_garnish where garnish_key
> not in (1, 2, 5))
>
>
> Thanks,
> Amber
>
>
