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 :-)

Example

```
TableA
User - Score
userA - 10
userB - 42
userC - 32
userB - 42
userB - 18
userD - 12
userB - 65

```

.

```
Table B

User - Color

userA - Green

userB - Yellow

userC - Blue

```

On these two tables I use a 

```
SELECT 
MAX(TableA.Score) AS MaxScore,
TableB.Color

FROM TableA

INNER JOIN TableB
ON TableA.User = TableB.User

GROUP BY TableA.User

```

The output here works nicely and is 

```
User - MaxScore - Color

UserA  - 10 - Green

UserB  - 65 - Yellow

UserC  - 32 - Blue

```

Now, I have a separate table where all the Scores get names.

```
TableC

Score - Name

5 - Quite Low

10 - OK

25 - Not bad

32 - Fairly good

50 - Well done

65 - Excellent

```

What I try to do is get a QUERY result which reads…

```
User - MaxScore - Name - Color

UserA - 10 - OK - Green

UserB - 65 - Excellent - Yellow

UserC - 32 - Fairly Good - Blue

```

My QUERY would look something like this…

```
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

```

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.

Looking forward to your wisdom.

Thanks :-)

Dutch