SHARE
TWEET

Untitled

a guest Jun 18th, 2019 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. match:
  2. id
  3. 1
  4. 2
  5.  
  6. playermatchdata:
  7. id | playerid | matchid | win (boolean) | team
  8. 1  | 1        | 1       | 1             | 1
  9. 2  | 2        | 1       | 1             | 1
  10. 3  | 3        | 1       | 0             | 0
  11. 4  | 1        | 2       | 0             | 0
  12. 5  | 4        | 2       | 0             | 0
  13. 5  | 5        | 2       | 1             | 1
  14. 6  | 5        | 3       | 1             | 0
  15. 7  | 1        | 3       | 0             | 1
  16.  
  17.  
  18. player:
  19. id | name
  20. 1  | player1
  21. 2  | luke
  22. 3  | bruce
  23. 4  | jon
  24. 5  | dan
  25.      
  26. SELECT count(playerid) as gamesplayed, player.name, count(win) as wins
  27. FROM match
  28. LEFT JOIN playermatchdata ON match.id = playermatchdata.matchid
  29. LEFT JOIN player ON player.id = playermatchdata.playerid
  30. WHERE win = 1 AND player.name = 'player1' AND team IN (
  31.      SELECT team
  32.      FROM playermatchdata
  33.      LEFT JOIN player ON player.id = playermatchdata.id
  34.      WHERE playermatchdata.matchid = pmd.matchid AND player.name = 'player1'
  35.  )
  36.  
  37. GROUP BY player.name
  38.      
  39. gamesplayed | name | wins
  40. 1           | dan  | 2
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top