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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
inner_query_test.py
Description: inner_query_test.py
