mySQL IF condition THEN condition

Question

I am struggling with the WHERE part of a query. The query itself contains a LEFT JOIN based on an ID that is present in both tables. However I require the where statement to only return the largest single result that is present in one of the columns. Currently I am return all the values in the join, including values that I do not want.

My Current SQL is

SELECT u.uid, t.id
GROUP_CONCAT(u.forename, ' ', u.surname) AS name,
GROUP CONCAT(DISTINCT scores.points) AS point
FROM users AS U
JOIN teamname AS t
   LEFT JOIN (
       SELECT team_id,  id
       FROM games AS g
       LEFT JOIN (
           SELECT points, team_id
           FROM scores as s
       ) AS S ON t.id = S.team_id
       WHERE IF (S.points > 3, S.points > 2, S.point =1)
   ) AS G ON t.id = G.team_id
ORDER BY surname ASC;

The result of such might be something along the lines of

NAME | TEAM | GAMES    | POINTS
Joe  | 1    | 1,2,3,4  | 1,3,3,2,3

In this instance the first game was a draw and was replied resulting in a higher points score, I am only wanting the higher points score based on that game.

Any help would be appreciated.

Updated with Tables

users
uid
forename
surname

Team
id
teamname
uid

games
id
team_id
points

Answer 1

Still not quite sure if I understood your tables correctly. It seems a users has one or more teams, each team has one or more games with one or more results per game. You want to show for each user and each team the games concatenated in one column and the highest points for each game concatenated in a second column.

If my assumptions are correct the following query should do the trick. Basically, you first group the data by user/team/game and select the max points per game, then you group the results by user/team and concatenate the games and points.

Please let me know if I misunderstood any of your requirements.

Example in an SQL Fiddle

SELECT
    t.uid,
    t.forename,
    t.team_id,
    GROUP_CONCAT(t.game_id) as games,
    GROUP_CONCAT(t.max_points) as max_points
FROM (
        SELECT
            users.uid,
            users.forename,
            teams.id AS team_id,
            games.id AS game_id,
            max(games.points) as max_points
        FROM
            users
            LEFT JOIN teams ON users.uid = teams.uid
            LEFT JOIN games ON teams.id = games.team_id
        GROUP BY
            users.uid,
            users.forename,
            teams.id,
            games.id
    ) t
GROUP BY
    t.uid,
    t.forename,
    t.team_id