query 1 : this will count all the record with email 'emali@test.com'
select count(*) as total from userbase
join extrauserinfofromhrms on userbase.username = extrauserinfofromhrms.useremail
right join logevent on userbase.username = logevent.useremail
join eachworkflow on logevent.workflowid= eachworkflow.workflowid
where logevent.actionname ='complete' and logevent.useremail like 'email@test.com'
query 2: list all user with emails..
select userbase.username from userbase
join extrauserinfofromhrms on userbase.username 开发者_如何转开发= extrauserinfofromhrms.useremail
how to use query2 to list all users with emailaddress and use each emailaddrses as parameter in query1?
SQL is a set-based language. It works best when we stop thinking in a row-by-row fashion. What you ought to be using is an aggregating query this:
select userbase.username
, count(*) as total
from userbase
join extrauserinfofromhrms on userbase.username = extrauserinfofromhrms.useremail
right join logevent on userbase.username = logevent.useremail
join eachworkflow on logevent.workflowid= eachworkflow.workflowid
where logevent.actionname ='complete'
and logevent.useremail like 'email@test.com'
group by userbase.username
/
精彩评论