Daniel Yantis Daniel Yantis - 2 months ago 6
MySQL Question

Multiple JOINs too complex

This has become more complex than I can follow. I have modified this query from another post here and now I need to add another join, but it's not working.

Here is the current query in MySQL:

SELECT * FROM (SELECT *,
@rn := IF(@prev = class, @rn + 1, 1) AS rn,
@prev := class
FROM HeatWaveData
JOIN
(SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY class, score DESC)
AS T1
WHERE rn <= 3
AND score > 0
AND dsqreason = ''
AND class <> ''


Now I need to
JOIN (SELECT * FROM Awards WHERE active ON Awards.award = HeatWaveData.award)

but it fails with errors. I've tried several changes and different things but keep getting errors.

Here is my updated query and error:

SELECT * FROM (SELECT *,
@rn := IF(@prev = class, @rn + 1, 1) AS rn,
@prev := class
FROM HeatWaveData
JOIN
(SELECT @prev := NULL, @rn := 0) AS vars
ORDER BY class, score DESC)
AS T1
LEFT JOIN
(SELECT * FROM Awards WHERE active = '1'
ON Awards.award = T1.award) AS T2
WHERE rn <= 3
AND score > 0
AND dsqreason = ''
AND class <> ''


Error:

error: #1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'ON Awards.award = T1.award) AS T2 WHERE rn <= 3 AN' at line 11

Answer

Just try to replace your sub-select with a "normal" join:

old:

LEFT JOIN
(SELECT * FROM Awards WHERE active = '1'
ON Awards.award = T1.award) AS T2

new:

LEFT JOIN Awards AS T2
ON T2.award = T1.award AND T2.active = '1'
Comments