complex combine sql

Question

query 1 : this will count all the record with email '[email protected]'

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 protected]'

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?

Answer 1

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 protected]' 
group by userbase.username 
/