Hello: I am new for SQLAlchemy and first time using with Plone. I need some help for left join.
I have two tables, roles and roleElements. Structure and sample data for these two tables I am including for easy popup of table/data which will be useful to him\her who will try to help me. ------------------------------------------------ CREATE TABLE `roles` ( `roleId` int(10) unsigned NOT NULL auto_increment, `name` varchar(20) default NULL, `description` varchar(500) default NULL, `isActive` char(1) default NULL, `modifiedOn` datetime default NULL, `modifiedBy` int(10) unsigned default NULL, `createdOn` datetime default NULL, `createdBy` int(10) unsigned default NULL, PRIMARY KEY (`roleId`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; --------------------------------------------- insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (1,'Sys Admin','Sys Admin','Y','2007-06-01 13:42:15', 1,'2007-05-21 17:23:40',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (2,'President','Employee. Org Group. Owner of President Space and all sub spaces.','Y','2007-05-22 11:33:18',1,'2007-05-21 17:26:21', 1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (3,'VP','Employee.\r\nOrg Group.\r\nOwner of VP Space & all sub spaces.','Y','2007-05-22 11:33:36',1,'2007-05-21 17:27:39',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (4,'Division Head','Employee.\r\nOrg Group.\r\nOwner of Division Space & all sub spaces.','Y',NULL,NULL,'2007-05-21 17:29:54', 1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (5,'Department Head','Employee.\r\nOrg Group.\r\nOwner of Department Space & all sub spaces.','Y',NULL,NULL,'2007-05-21 17:31:42',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (6,'Instructor','Employee.\r\nOrg Group.\r\nCRN Assigned.\r \nOwner of CRN Space & all sub spaces.','Y',NULL,NULL,'2007-05-21 17:32:50',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (7,'Other Employee','Enployee.\r\nOrg Group.\r\nOwner of Projects/sub spaces.','Y',NULL,NULL,'2007-05-21 17:33:42',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (8,'Student','Student.\r\nOrg Group.\r\nOwner of Student Projects/sub spaces.','Y','2007-05-21 17:36:51',1,'2007-05-21 17:35:01',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (9,'Item Owner','Any User. Creator of file. Owns the file.','Y','2007-05-22 11:34:53',1,'2007-05-21 17:37:49',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (10,'Workspace Owner','Any user. \r\nOwner of Space & its sub spaces.','Y','2007-05-22 11:39:43',1,'2007-05-21 17:38:36',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (11,'Contributor','Any User.\r\nContributor to the files.','Y','2007-05-22 11:40:22',1,'2007-05-21 17:40:32',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (12,'Approver','Any User.\r\nApproves/Rejects a file.','Y','2007-05-22 11:40:57',1,'2007-05-21 17:41:15',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (13,'Reviewer','Any user.\r\nReviews files & enters comments.','Y','2007-05-22 11:41:25',1,'2007-05-21 17:42:19',1); insert into `roles` (`roleId`,`name`,`description`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (15,'Test','Test','Y','2007-06-01 15:47:46',1,'2007-06-01 15:47:15',1); -------------------------------------------------- -------------------------------------------------- CREATE TABLE `roleElements` ( `roleElementId` int(10) unsigned NOT NULL auto_increment, `roleId` int(10) unsigned default NULL, `elementId` int(10) unsigned default NULL, `elementLevelId` int(10) unsigned default NULL, `isActive` char(1) default NULL, `modifiedOn` datetime default NULL, `modifiedBy` int(10) unsigned default NULL, `createdOn` datetime default NULL, `createdBy` int(10) unsigned default NULL, PRIMARY KEY (`roleElementId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ----------------------------------------------------- insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (1,2,1,1,'Y',NULL,NULL,'2007-05-24 15:45:25',1); insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (2,3,1,1,'Y','2007-05-26 11:49:30',1,'2007-05-24 15:55:36',1); insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (3,4,1,1,'Y','2007-05-26 11:50:10',1,'2007-05-24 16:19:26',1); insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (4,5,1,1,'Y','2007-05-26 11:50:10',1,'2007-05-24 18:34:02',1); insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (5,9,1,1,'Y',NULL,NULL,'2007-05-25 17:23:02',1); insert into `roleElements` (`roleElementId`,`roleId`,`elementId`,`elementLevelId`,`isActive`,`modifiedOn`,`modifiedBy`,`createdOn`,`createdBy`) values (6,3,1,2,'Y',NULL,NULL,'2007-05-26 10:40:49',1); --------------------------------------------------- --------------------------------------------------- SQL query for left join which give my desire result is as follow. How I can convert it to make it work with SQLAlchemy. 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 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 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 -~----------~----~----~----~------~----~------~--~---
