How to use max value in the next join in a query?

Question

SELECT
v.id as variateId,
v.name as variateName,
createdAt,
hideInAdmin,
IF(IFNULL(mvv.variateID,0) > 0,'1','0') AS inuse,
decisionTreeNodeID,
vdtn.decisionTreeNodeID,
mvv.campaignIDs,
leadid,
l.id,
ls.variateID,
l.originaldate,
l.originaltime,
v.useAlternateRouting
FROM Variates v
LEFT JOIN VariatesDecisionTreeNodes vdtn
ON vdtn.variateID = v.id
AND vdtn.rootNodeID IS NOT NULL
AND vdtn.rootNodeID = vdtn.decisionTreeNodeID
AND vdtn.version=0
LEFT JOIN
(SELECT
mvv.variateID,
GROUP_CONCAT(cmv.campaignID) AS campaignIDs
FROM MultiVariatesVariates mvv
INNER JOIN CampaignsMultiVariates cmv
ON cmv.multiVariateID = mvv.multiVariateID
GROUP by mvv.variateID
) mvv ON mvv.variateID = v.id
LEFT JOIN
(SELECT
ls.id,
MAX(ls.leadID) as leadid,
ls.variateID
FROM LeadStrategies AS ls
GROUP BY ls.variateID
) ls ON ls.variateID = v.id

LEFT JOIN
(SELECT
l.id,
DATE_FORMAT(l.originaldate, '%Y-%m-%d') AS originaldate,
DATE_FORMAT(l.originaltime, '%h:%i %p') AS originaltime
FROM Leads AS l
) l ON l.id = leadid;

above is a query contain MAX(ls.leadID) and i want to use it in next join.

Answer 1

Try to use a view to access that value.

WITH lsview as (SELECT
ls.id,
MAX(ls.leadID) as leadid,
ls.variateID
FROM LeadStrategies AS ls
GROUP BY ls.variateID)
SELECT
v.id as variateId,
v.name as variateName,...
.......
So you can access the view as if it is a table:

...........
) mvv ON mvv.variateID = v.id
LEFT JOIN
lsview ON ls.variateID = v.id
.......
SELECT lsview.leadid ....