finally I did sub query as follow, and worked. (Where, vcc_schema is my database schema0
subquery_stmt = select([vcc_schema.RoleElementsTable.c.roleId],and_(vcc_schema.RoleElementsTable.c.elementId == elementsId,vcc_schema.RoleElementsTable.c.elementLevelId == elementLevelId,vcc_schema.RoleElementsTable.isActive== 'Y')) query_stmt = select([vcc_schema.RolesTable.c.roleId,vcc_schema.RolesTable.c.name, vcc_schema.RolesTable.c.description], not_(vcc_schema.RolesTable.c.roleId.in_(subquery_stmt))) Hope it will help others like me.. On Jun 4, 9:14 am, aruna-cgx <[EMAIL PROTECTED]> wrote: > SQL query for left join which give my desire result is as follow. i > can use left join or sub select, though I prefer to use left join but > I am Ok with subselect also. > > I don't know how to write this query in sqlalchemy. I refered > document, but my query differes on join key as I have more than one > filter on join key. > . > > SELECT R.roleId, R.name, R.description from roles R > left join roleElements RE on R.roleId = RE.roleId and RE.isActive = > 'Y' and RE.elementId = 1 and RE.elementLevelId = 1 > where RE.roleId IS NULL > > OR > > SELECT * > FROM roles > WHERE roleId not in > (select roleId from roleElements where elementLevelId = 1 and > elementId = 1 and isActive= "Y") > > note: In actual query, values of RE.elementId and RE.elementLevelId > would be variable. > > I really stuck here and tried a lot but couldn't get it. > Hopping any help. > > Thank you in advance. > > Aruna Kathiriya --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
