I am getting the below output, I want the same out but without subqueries.I am looking to refine the below query by removing the subquery as it is affecting the performance of the application. Could anyone help me to remove subquery and fetch the required data using the joins.
SELECT DISTINCT rec.requestno as requestno
, (SELECT min(dateandtime)
from recruitersubmission
where recruitersubmission.requestno = rec.requestno) as firstsubmsion
, (SELECT max(dateandtime)
from recruitersubmission
where recruitersubmission.requestno = rec.requestno) as lastsubmission
, cndinterview.dateandtime as candidatefedtime
, cndinterview.dateandtime AS recruitertime
, CONCAT(availabledate,' ', availabletime) AS candidateavaibledatetime
, cndfeedback.status AS status
, cndfeedback.offereddate AS offereddate
, cnd.status AS onboard
, (SELECT COUNT(recruitersubmission.requestno)
FROM recruitersubmission
WHERE recruitersubmission.requestno=rec.requestno) AS totalSub
, (select COUNT(candidatefeedback.requestno)
from candidatefeedback
WHERE candidatefeedback.requestno = rec.requestno
&& candidatefeedback.status = 'Selected' ) as totalsel
, (select COUNT( candidatefeedback.requestno)
from candidatefeedback
WHERE candidatefeedback.requestno = rec.requestno
&& cndfeedback.status = 'Rejected' ) as totalrej
, (select COUNT(candidatefeedbkonboard.requestno)
from candidatefeedbkonboard
WHERE candidatefeedbkonboard.requestno = rec.requestno
&& cnd.status = 'Drop' ) as totaldrop
, (select COUNT(candidatefeedbkonboard.requestno)
from candidatefeedbkonboard
WHERE candidatefeedbkonboard.requestno = rec.requestno
&& candidatefeedbkonboard.status = 'Onboarded' ) as totalonboard
from recruitersubmission AS rec
LEFT JOIN candidatefeedbkonboard AS cnd
ON rec.requestno=cnd.requestno
LEFT JOIN candidatefeedback AS cndfeedback
ON rec.requestno=cndfeedback.requestno
LEFT JOIN candidatesinterview AS cndinterview
ON rec.requestno=cndinterview.requestno
where rec.clientname = '$client'
&& rec.requestno != ''
&& rec.country = '$location'
&& date(rec.dateandtime) between '$fromdate'
I want to avoid these below subqueries.
(select COUNT(candidatefeedback.requestno) from candidatefeedback WHERE candidatefeedback.requestno = rec.requestno && candidatefeedback.status = 'Selected' ) as totalsel
CodePudding user response:
Thank you for the help friends. I figured out how to do it. Below is the solution for your refernce.
SELECT
rec.requestno,
COALESCE(totalsub, 0) AS totalsub,
COALESCE(totalsel, 0) AS totalsel,
COALESCE(totalrej, 0) AS totalrej
FROM recruitersubmission rec
LEFT JOIN (
SELECT requestno, COUNT(*) AS totalsub
FROM recruitersubmission
GROUP BY requestno
) recruitersubmission ON recruitersubmission.requestno = rec.requestno
LEFT JOIN (
SELECT requestno,count(if(status = 'Selected',1,Null)) AS totalsel,count(if(status = 'Rejected',1,Null)) as totalrej
FROM candidatefeedback
GROUP BY requestno
) candidatefeedback ON candidatefeedback.requestno = rec.requestno
GROUP by rec.requestno Limit 0,25