Hi, I have trouble on making a query between two tables:
ProcessedDataset - ID ProcDSParent - ThisDataset - ItsParent To find the `Parent` and `Child` of a `Dataset`, I need explicit specify the join sequence between: `ProcessedDataset` <-- `ProcDSParent` --> `ProcessedDataset` After make different alias for them, I try different join approach, but still not able to get it. I will demonstrate with find `Parent` of a `Dataset`: --------------------------------------------------------------------- dataset = DB.db_tables[db_alias]['ProcessedDataset'] dsparent = DB.db_tables[db_alias]['ProcDSParent'] parent = dataset.alias('ProcessedDataset_ItsParent') child_parent = dsparent.alias('ThisDataset_ItsParent') keylist = [parent.c.Name] whereclause = dataset.c.Name.op('=')('Test') --------------------------------------------------------------------- --- 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) --------------------------------------------------------------------- * result <1>: =============================================== SELECT `ProcessedDataset_ItsParent`.`Name` FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`, `ProcessedDataset` WHERE `ProcessedDataset`.`Name` = %s =============================================== --- Approach <2>: * orm.join + expression.select --------------------------------------------------------------------- join2 = join(dataset, child_parent, \ dataset.c.ID == child_parent.c.ThisDataset) join2.join(dsparent, \ child_parent.c.ItsParent == parent.c.ID) --------------------------------------------------------------------- * result <2>: =============================================== SELECT `ProcessedDataset_ItsParent`.`Name` FROM `ProcessedDataset` AS `ProcessedDataset_ItsParent`, `ProcessedDataset` INNER JOIN `ProcDSParent` AS `ThisDataset_ItsParent` ON `ProcessedDataset`.`ID` = `ThisDataset_ItsParent`.`ThisDataset` WHERE `ProcessedDataset`.`Name` = %s =============================================== Appreciate for any help, stuck here for couple of days already! -- 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.