-1

Given the following query which contains a subquery:

SELECT COUNT(DISTINCT sc.caseID) AS 'TotalCases',  

(SELECT COUNT(DISTINCT se1.caseID)        
 FROM scEvent AS se1          
 INNER JOIN scCase AS sc1 
         ON (se1.caseID = sc1.caseID AND 
             sc1.vendorID = '258' )   <-- this is what I want to make dynamic

  WHERE sc1.vendorID = sc.vendorID
  AND ( se1.escalationLevel = 1 OR
        sc.escalationLevel = 1) ) AS 'level1Count'


FROM scCase AS sc 

WHERE sc.vendorID = '258'

If I try to change the inner join on the subquery to be dynamic like this:

SELECT COUNT(DISTINCT sc.caseID) AS 'TotalCases', 

(SELECT COUNT(DISTINCT se1.caseID)        
 FROM scEvent AS se1          
 INNER JOIN scCase AS sc1 
         ON (se1.caseID = sc1.caseID AND 
             sc1.vendorID = sc.vendorID ) <-- only thing that's different

  WHERE sc1.vendorID = sc.vendorID
  AND ( se1.escalationLevel = 1 OR
        sc.escalationLevel = 1) ) AS 'level1Count'


FROM scCase AS sc 

WHERE sc.vendorID = '258'

Then I get the error: Unknown column 'sc.vendorID' in 'on clause'

How can I make the vendor ID in the correlated subquery dynamic depending on the outer record. Is it even possible?

  • 1
    Do you get the same error if you move the condition to the WHERE clause?: AND sc1.vendorID = sc.vendorID Oh wait, you already have it there! Why does it need to go in the ON too? – ypercubeᵀᴹ May 22 at 16:19
  • Your comment got me thinking. I don't even need the vendor ID in the join. – S.Mason May 22 at 16:25
1

I'm not really sure what exactly the query is trying to count but you can most liekly rewrite much more simply.

  • One idea would be to get rid of the subquery in the SELECT and put the join in the main FROM clause.

  • Another would be to remove the table that is duplicated from the internal FROM.

Script for 2nd idea:

SELECT
  COUNT(DISTINCT sc.caseID) AS TotalCases, 

  ( SELECT COUNT(DISTINCT se1.caseID)        
    FROM scEvent AS se1          
    WHERE se1.caseID = sc.caseID
      AND ( se1.escalationLevel = 1
         OR sc.escalationLevel = 1
          )
  ) AS level1Count

FROM scCase AS sc 

WHERE sc.vendorID = '258' ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.