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 -~----------~----~----~----~------~----~------~--~---