2
SELECT player.*, player_iplog.* 
FROM player 
LEFT JOIN player_iplog ON player.SteamID=player_iplog.SteamID 
WHERE player.Rank = 'superadmin'

I have the above query in a PHP script that displays a player's statistics from a MySQL database. Everything works okay, however, some players have duplicate entries

I cannot remove the duplicate entries, as it is a feature and not a bug.

Is there any way this query could be modified to only return each user once?

I have this hooked up with a while ( $res->fetch-array() ) {} loop, so it displays every user's entry multiple times.

Also, I have tried DISTINCT to no avail.

Barmar
  • 741,623
  • 53
  • 500
  • 612
TylerD
  • 23
  • 4
  • Change it from an outer join to a normal (inner) join and make additions to the where clause? Or order by player and sort it out in the PHP if the same player is picked up again? – Fluffeh May 31 '14 at 00:04
  • Try to add `group by player.*, player_iplog.*` at the end of the query. You might have to name the fields that you want to grab in order for it to work. – Nir Alfasi May 31 '14 at 00:10

1 Answers1

1

You can group by player:

SELECT player.*, player_iplog.* 
FROM player 
LEFT JOIN player_iplog ON player.SteamID=player_iplog.SteamID 
WHERE player.Rank = 'superadmin'
GROUP BY player.SteamID

or you can do it with a subquery:

SELECT player.*, player_iplog.*
FROM player
LEFT JOIN (SELECT *
           FROM player_iplog
           GROUP BY SteamID) AS player_iplog
ON player.SteamID = player_iplog.SteamID
WHERE player.Rank = 'superadmin'

This assumes the duplicates are only in the player_iplog table, not the player table.

If superadmins are a small fraction of all players, the first version will probably be more efficient, since that will limit the size of the join.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks, this works. However, I need the max value from the player_iplog table, the LastSeen column to be specific. I can't find where to put the MAX() function. – TylerD May 31 '14 at 00:54
  • That's a completely different question now. It's not just removing duplicates. – Barmar May 31 '14 at 01:00
  • See http://stackoverflow.com/questions/23963652/sql-max-with-inner-joins/23964188#23964188 for how to do that. – Barmar May 31 '14 at 01:02