I found out the solution by explicitly adding join condition: eq1 = ad_equipment_table.alias() eq2 = ad_equipment_table.alias() e1 = equipment_table.alias() e2 = equipment_table.alias()
ad_table.join(eq1, join_condition(ad_table, eq1)).join(e1, join_condition(eq1, e1)).join(eq2, join_condition(ad_table, eq2)).join(e2, join_condition(eq2, e2)) On Wed, Apr 29, 2009 at 7:12 PM, tomas <to...@drencak.com> 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' > ) > > How can I do this kind of select? I've tried something like > 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 is not I wanted as resulting SQL joins for e1 and e2 are > wired together... > > Is it possible to create this select by SA expression? Or is there a > better way how to do that? > > thanks, > tomas > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
