CODEWITHSUNDEEP
sqlinner-join
Yogi Septiargy
Yogi Septiargy

Reputation: 27

SQL Triple Inner Join

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

Upvotes: 2

Views: 125

Answers (2)

somaye javidmoradi
somaye javidmoradi

Reputation: 197

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

Upvotes: 3

Mohsin Afzal
Mohsin Afzal

Reputation: 31

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

Upvotes: 0

Related Questions