Dear Michael, Appreciate for help me solving this problem.
Meanwhile I found something interesting, maybe worth to tell you. Two alias object of same table with same name are treated as conflicts when both using in query. Error 1066, "Not unique table/alias is throwing. Many regards, Dong =============================================== dataset = DB.db_tables[db_alias]['ProcessedDataset'] parent = dataset.alias('ProcessedDataset_ItsParent') # First Time keylist = [parent.c.Name] whereclause = dataset.c.Name.op('=')('Summer09-MC_31X_V3-v1') query = select(keylist, whereclause) dsparent = DB.db_tables[db_alias]['ProcDSParent'] parent = dataset.alias('ProcessedDataset_ItsParent') # Second Time child_parent = dsparent.alias('ThisDataset_ItsParent') r_join = dataset r_join = r_join.join(child_parent, dataset.c.ID == child_parent.c.ThisDataset) r_join = r_join.join(parent, child_parent.c.ItsParent == parent.c.ID) print r_join query.append_from(r_join) print query DB.execute(query) =========================================== SELECT `ProcessedDataset_ItsParent`.`Name` FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`, `ProcessedDataset` INNER JOIN `ProcDSParent` AS `ThisDataset_ItsParent` ON `ProcessedDataset`.`ID` = `ThisDataset_ItsParent`.`ThisDataset` INNER JOIN `ProcessedDataset` AS `ProcessedDataset_ItsParent` ON `ThisDataset_ItsParent`.`ItsParent` = `ProcessedDataset_ItsParent`.`ID` WHERE `ProcessedDataset`.`Name` = %s Traceback (most recent call last): File "testJoin.py", line 52, in <module> DB.execute(query) File "/data/Python/lib/python2.6/site-packages/PyQB-1.0.0-py2.6.egg/pyquerybuilder/db/DBManager.py", line 448, in execute result = self.con.execute(query) File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1405, in execute File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1538, in _execute_clauseelement File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1646, in _execute_context File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1639, in _execute_context File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/default.py", line 330, in do_execute File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in execute File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler sqlalchemy.exc.OperationalError: (OperationalError) (1066, "Not unique table/alias: 'ProcessedDataset_ItsParent'") 'SELECT `ProcessedDataset_ItsParent`.`Name` \nFROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`, `ProcessedDataset` INNER JOIN `ProcDSParent` AS `ThisDataset_ItsParent` ON `ProcessedDataset`.`ID` = `ThisDataset_ItsParent`.`ThisDataset` INNER JOIN `ProcessedDataset` AS `ProcessedDataset_ItsParent` ON `ThisDataset_ItsParent`.`ItsParent` = `ProcessedDataset_ItsParent`.`ID` \nWHERE `ProcessedDataset`.`Name` = %s' ('Summer09-MC_31X_V3-v1',) On Wed, Mar 7, 2012 at 12:01 AM, Michael Bayer <mike...@zzzcomputing.com>wrote: > > On Mar 6, 2012, at 1:22 AM, Liangd wrote: > > > --- Approach <1>: > > * FromClause.join + expression.select > > --------------------------------------------------------------------- > > > > r_join = dataset > > r_join.join(child_parent, \ > > dataset.c.ID == child_parent.c.ThisDataset) > > r_join.join(dsparent, \ > > child_parent.c.ItsParent == parent.c.ID) > > > > query = select(keylist, from_obj=r_join, whereclause=whereclause) > > --------------------------------------------------------------------- > > > the join() method returns a new Join object, that's the one which > represents the join. The original object is unchanged: > > r_join = dataset > r_join = r_join.join(child_parent, ...) > r_join = r_join.join(dsparent, ...) > > > -- Liang Dong Alias: lia...@cern.ch From: Institute of High Energy Physics Chinese Academy of Sciences. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.