+ 2

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

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.

24th May 2019, 10:50 AM
Muhammad Idrees 🇵🇰
Muhammad Idrees 🇵🇰 - avatar
1 Odpowiedź
0
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 ....
10th Jan 2020, 10:40 AM
Simone
Simone - avatar