SQL Triple Inner Join

user_13744601 0
Viewed 69

I want List all players whose global levels more than 5 and have played more than 3 matches

SELECT Players.Nickname, Accounts.Email, players.GlobalLevel
FROM Players
INNER JOIN Accounts on Accounts.AccountID = Players.PlayerID
INNER JOIN PlayHistory on Players.PlayerID = PlayHistory.PlayerID
WHERE Players.GlobalLevel >5 AND PlayHistory.MatchID >3
ORDER BY Players.Nickname ASC

But the result gives me this. There are multiples name that selected

result

inner-join sql
2 Answers

you need the count of matches is more than 3 not match_id:

SELECT Players.Nickname, Accounts.Email, 
players.GlobalLevel,count(PlayHistory.MatchID) matchCount
FROM Players
INNER JOIN Accounts on Accounts.AccountID = Players.PlayerID
INNER JOIN PlayHistory on Players.PlayerID = PlayHistory.PlayerID
WHERE Players.GlobalLevel >5 
GROUP BY  Players.Nickname, Accounts.Email, players.GlobalLevel
HAVING count(PlayHistory.MatchID) >3
ORDER BY Players.Nickname ASC
user_136282170

Try This -

       SELECT Players.Nickname, Accounts.Email, players.GlobalLevel
      FROM Players
                                      --Replace PlayerId to AccountId
      INNER JOIN Accounts on Accounts.AccountID = Players.AccountID   
      
     INNER JOIN PlayHistory on Players.PlayerID = PlayHistory.PlayerID
       WHERE Players.GlobalLevel >5 AND PlayHistory.MatchID >3
      ORDER BY Players.Nickname ASC
user_75921920
Related