CODEWITHSUNDEEP
mysql
Daniel Yantis
Daniel Yantis

Reputation: 167

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

Upvotes: 1

Views: 46

Answers (1)

rbr94
rbr94

Reputation: 2287

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'

Upvotes: 2

Related Questions