the subquery is created first as its own object, which then gains its
own .c. attribute. the only thorny aspect is the correlate(None)
which disables the inner 'organization' from correlating to the outer
one.
from sqlalchemy import select
from sqlalchemy.sql import table, column
organization = table('organization', column('id'), column('name'))
email = table('email', column('id'), column('address'))
organization_email = table('organization_email',
column('organization_id'), column('email_id'))
email_address = select([
email.c.address.label('email'),
organization.c.id.label('org_id')]).where(
organization.c.id == organization_email.c.organization_id
).where(email.c.id==organization_email.c.email_id).distinct().\
correlate(None).limit(1).\
alias('email_address')
print select([
organization.c.name, email_address.c.email])\
.select_from(
organization.outerjoin(email_address,
organization.c.id==email_address.c.org_id)
)
On Dec 1, 2008, at 8:51 PM, [EMAIL PROTECTED] wrote:
>
> When using SQLAlchemy SQL Expressions, what's the appropriate syntax
> for doing a left join on a sub-query so that you can select results
> from the sub-query and include them in the results for the main query
> e.g.:
>
> SELECT organization.name, email_address.email,
> FROM organization
> LEFT JOIN (
> SELECT DISTINCT email.address AS email, organization.id AS org_id
> FROM email, organization, organization_email
> WHERE organization.id = organization_email.organization_id AND
> email.id = organization_email.email_id
> LIMIT 1
> ) email_address ON email_address.org_id = organization.id
>
> -lcr
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
