I've forgotten to attach the file with tables...
On Wed, Apr 29, 2009 at 4:12 PM, Tomáš Drenčák <[email protected]> wrote:
> Hi,
> I've got table Ad which has many to many relation with table Equipment
> through table ad_equipment. I want to select all ads which have all
> equipments in the list.
>
> Corresponding SQL query will look like this:
>
> select * from ad where ad.id in (
> select id from ad
> join ad_equipment eq1 on ad.id = eq1.ad
> join equipment e1 on eq1.equipment = e1.id
> join ad_equipment eq2 on ad.id = eq2.ad
> join equipment e2 on eq2.equipment = e2.id
> where e1.name = 'name1' and e2.name = 'name2'
> )
>
> I've tried this the following:
> eq1 = ad_equipment_table.alias()
> eq2 = ad_equipment_table.alias()
> e1 = equipment_table.alias()
> e2 = equipment_table.alias()
>
> ad_table.join(eq1).join(e1).join(eq2).join(e2)
>
> but this corresponds to:
> select * from ad
> JOIN ad_equipment AS ad_equipment_1 ON ad.id = ad_equipment_1.ad
> JOIN equipment AS equipment_1 ON equipment_1.id = ad_equipment_1.equipment
> JOIN ad_equipment AS ad_equipment_2 ON equipment_1.id =
> ad_equipment_2.equipment AND ad.id = ad_equipment_2.ad
> JOIN equipment AS equipment_2 ON equipment_2.id =
> ad_equipment_1.equipment AND equipment_2.id = ad_equipment_2.equipment
>
> which is not what I want as it joins e2 to be the same as e1...
>
> Is it possible to write that SQL select in SA expression language?
>
> thanks,
> tomas
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import MetaData, Integer, String, Table, ForeignKey, Column
metadata = MetaData()
equipment_table = Table('equipment', metadata,
Column('id', Integer, primary_key=True),
Column('name', String),
)
ad_equipment_table = Table('ad_equipment', metadata,
Column('id', Integer, primary_key=True),
Column('ad', ForeignKey('ad.id')),
Column('equipment', ForeignKey('equipment.id')),
)
ad_table = Table('ad', metadata,
Column('id', Integer, primary_key=True),
)
