November 2nd, 2012, 01:06 PM
Query crossing multiple table
Hi all hope someone can help with this.
I have the following query
select cscommprofilesetid,count(*) cscommprofilesetid from cshandle where cshandletypeid=57 group by cscommprofilesetid
which outputs this
cscommprofilesetid | cscommprofilesetid
403 | 1
402 | 2
404 | 1
However I need cscommprofilesetid to be replaced with a user ID, I can do this with this statment.
select loginname from csuser where csuser.id IN (select csuserid from cscommprofileset where cscommprofileset.id = '402')
but no matter what I try I cannot get this to work. The closest I got is this
select loginname,cscommprofilesetid from csuser,cshandle
> where cshandletypeid=57 AND csuser.id IN (select csuserid from cscommprofileset) group by cscommprofilesetid,loginname;"
but this just outputs every row with a count thats not what I wanted.
Can anyone help?
November 8th, 2012, 10:55 PM
You can try this
select (select csuserid from cscommprofileset where id=cscommprofilesetid) as csuserid,count(*) cscommprofilesetid from cshandle where cshandletypeid=57 group by 1;