That did it - thanks a lot Simon
> -----Original Message----- > From: sqlalchemy@googlegroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of Michael Bayer > Sent: 06 March 2007 14:38 > To: sqlalchemy@googlegroups.com > Subject: [sqlalchemy] Re: Table being removed from nested query > > > 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 -~----------~----~----~----~------~----~------~--~---
