More examples using Correlated Subquery

More examples using Correlated Subquery-

In my opinion, although Method 1 has the extra select statement at the top, it conveys the logic of the query better than Method 2.

In Method 2, we basically have pushed the 'join' statement inside the outer query.


Method 1:



SELECT 
A.*, Challenge.* 
FROM 
(SELECT CE1.Challenge_ID,Question1,Question2,Question3,Question4,Question5,Question6
FROM Challenge_Enter as CE1
WHERE CE1.ID = 
(SELECT max(CE2.ID) 
From Challenge_Enter as CE2
WHERE CE2.CustomerID = 1123607 AND CE2.Challenge_ID = CE1.Challenge_ID AND Challenge_ID > 0)
) A
INNER JOIN 
Challenge 
on A.Challenge_ID = Challenge.ID



Method 2:



SELECT Challenge.*,CE1.Challenge_ID,Question1,Question2,Question3,Question4,Question5, Question6
FROM Challenge_Enter as CE1
INNER JOIN Challenge on CE1.Challenge_ID = Challenge.ID 
WHERE CE1.ID = 
(SELECT max(CE2.ID) 
From Challenge_Enter as CE2
WHERE CE2.CustomerID = 1123607 AND CE2.Challenge_ID = CE1.Challenge_ID AND Challenge_ID > 0)