MySQL INNER JOIN with SubQuery (well detailed)
<p>I'm trying to add a third INNER JOIN to a SELECT Query, where this third INNER JOIN needs to look for a value in that third Table, which value needs to be coupled with the outcome of a MAX + GROUP BY construct in the main SELECT Query. This may not be possible, I don’t know. I can't get it to work :-)</p>

<p>Example</p>

<pre><code>TableA
User - Score
userA - 10
userB - 42
userC - 32
userB - 42
userB - 18
userD - 12
userB - 65
</code></pre>

<p>.</p>

<pre><code>Table B

User - Color

userA - Green

userB - Yellow

userC - Blue
</code></pre>

<p>On these two tables I use a </p>

<pre><code>SELECT 
MAX(TableA.Score) AS MaxScore,
TableB.Color

FROM TableA

INNER JOIN TableB
ON TableA.User = TableB.User

GROUP BY TableA.User
</code></pre>

<p>The output here works nicely and is </p>

<pre><code>User - MaxScore - Color

UserA  - 10 - Green

UserB  - 65 - Yellow

UserC  - 32 - Blue
</code></pre>

<p>Now, I have a separate table where all the Scores get names.</p>

<pre><code>TableC

Score - Name

5 - Quite Low

10 - OK

25 - Not bad

32 - Fairly good

50 - Well done

65 - Excellent
</code></pre>

<p>What I try to do is get a QUERY result which reads…</p>

<pre><code>User - MaxScore - Name - Color

UserA - 10 - OK - Green

UserB - 65 - Excellent - Yellow

UserC - 32 - Fairly Good - Blue
</code></pre>

<p>My QUERY would look something like this…</p>

<pre><code>SELECT 
TableA.User
MAX(TableA.Score) AS MaxScore,
TableC.Name,
TableB.Color

FROM TableA

INNER JOIN TableB
ON TableA.User = TableB.User

INNER JOIN TableC
ON TableC.Score = (SELECT MAX(TableA.Score) AS MaxScore FROM Table A GROUP BY TableA.User) ThisSubQueryName

GROUP BY TableA.User
</code></pre>

<p>My problem is clearly with the syntax of the INNER JOIN of Table C, which INNER JOIN carries that subquery. I have no idea how to do this, or if it is even possible.</p>

<p>Looking forward to your wisdom.</p>

<p>Thanks :-)</p>

<p>Dutch</p>