[sqlalchemy] Re: Left join or subselect syntax(more than one filter on join key)

[aruna-cgx]

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 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
-~--~~~~--~~--~--~---

[sqlalchemy] Left join or subselect syntax(more than one filter on join key)

[aruna-cgx]

 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 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
-~--~~~~--~~--~--~---

[sqlalchemy] need help for left join

[aruna-cgx]

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);