CODEWITHSUNDEEP
mysql
tawheed
tawheed

Reputation: 5821

Querying a many to many relationship in SQL

I have the following SQL relationship

user has many games games has may users

User
----
id |  name | age |
__________________
1  |  mike |  11 |
2  |  jeff |  12 |
3  |  jake |  31 |
4  |  lemd |  81 |

Game
-----
id |  name  | time  |
_____________________
1  |  froyo | 11:10 |
2  |  honey | 12:22 |
3  |  combb | 13:00 |
4  |  lolli | 14:00 |


User_Game
----------
| userid  | game_id |
 ___________________
|   1     |   2     |
|   2     |   2     |
|   3     |   1     |
|   4     |   3     |
|   1     |   2     |
|   2     |   4     |
|   2     |   1     |

For each of the users is there a way to get a list of games that they have played including the number of games that each user participated in.

Edit

I tried this query

Select User.name, User.age
  from User  
  inner join User_Game 
  on User.id=User_Game.userid;

However not sure how I could add the count to it

Upvotes: 0

Views: 49

Answers (1)

sn00k4h
sn00k4h

Reputation: 443

SELECT 
    userid, 
    GROUP_CONCAT(game_id) as game_list,
    COUNT(*) as total_games
FROM 
    USER_GAME 
GROUP BY 
    userid;

Upvotes: 2

Related Questions