Assuming a declarative based class USER exists, then you can join each of the queries q1, q2, q3 to USER like this:
q1 = session.query(P1.userid,P1.extra,P1.title,P1.body,USER.email) q1 = q1.join((USER,USER.userid==P1.userid)) q2 = session.query(P2.userid,"'X'",P2.title,P2.body,USER.email) q2 = q2.join((USER,USER.userid==P2.userid)) q3 = session.query(P3.userid,"'X'",P3.title,P3.body,USER.email) q3 = q3.join((USER,USER.userid==P3.userid)) q=q1.union_all(q2,q3) Not a very elegant solution, and probably leads to an inefficient query plan in many databases. Can anyone tell us how to join the result of union_all with another table? Probably a subquery()? Effectively: - create q1, q2, q3 as selects from P1, P2, P# as in original solution - combine q1, q2, q3 with a union_all() - add column USER.email to the query - join resulting query to USER based on userid column in the union_all statement SQL would look something like this: SELECT qry.a, qry.b, qry.c, user.x FROM (SELECT a,b,c FROM p1 UNION ALL SELECT a,b,c FROM p2 UNION ALL SELECT a,b,c FROM p3) as qry JOIN USER on qry.a = USER.a but I can't seem to get this result in SQLAlchemy -- Mike Conley --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
