try putting "correlate=False" in the nested select.
On Mar 6, 2007, at 6:29 AM, King Simon-NFHD78 wrote:
> Hi,
>
> I have a problem in which a table is being removed from the FROM
> clause
> of a nested query. The attached file should show the problem, which
> I've
> tested on 0.3.5 and rev 2383.
>
> In the example, there are two tables, department and employee, such
> that
> one department has many employees. The inner query joins the two
> tables
> and returns department IDs:
>
> inner = select([departments.c.department_id],
> employees.c.department_id ==
> departments.c.department_id)
> inner = inner.alias('filtered_departments')
>
> The SQL looks like:
>
> SELECT departments.department_id
> FROM departments, employees
> WHERE employees.department_id = departments.department_id
>
> I then join this query back to the department table:
>
> join = inner.join(departments,
>
> onclause=inner.c.department_id==departments.c.department_id)
>
> SQL for the join condition looks like:
>
> (SELECT departments.department_id
> FROM departments, employees
> WHERE employees.department_id = departments.department_id)
> AS filtered_departments
> JOIN departments ON filtered_departments.department_id =
> departments.department_id
>
> This still looks correct to me. However, I then base a query on this
> join:
>
> outer = select([departments.c.name],
> from_obj=[join],
> use_labels=True)
>
> At this point, the 'departments' table is no longer part of the inner
> query. The SQL looks like:
>
> SELECT departments.name
> FROM (SELECT departments.department_id AS department_id
> FROM employees
> WHERE employees.department_id = departments.department_id)
> AS filtered_departments
> JOIN departments ON filtered_departments.department_id =
> departments.department_id
>
> ...and the query doesn't run.
>
> I think I can work around it by putting the join condition in the
> whereclause of the select, instead of from_obj, but is there a reason
> why the join version doesn't work?
>
> Thanks,
>
> Simon
>
> >
> <inner_query_test.py>
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
