CODEWITHSUNDEEP
mysqlsqlmany-to-many
MadTyrael
MadTyrael

Reputation: 125

MYSQL - Query many to many relation on multiple results

I have two tables (player and group) with a many to many relation and a junction table (player_group):

Table "player"

Table "player"
Table "group"

Table "group"
and the junction table:

Table "player_group"

Table "player_group"

A player can be part of many groups and a group can have many players. A user can also be a group's admin represented by the flag "flg_admin".

I want to build a query that tells me, for a specific player (lets say player1 with id=27):

So for player1 (ID=27) the result would be:

|group_name|no. members|flg_admin|
----------------------------------
|GROUP A   |         2 |        1|
|GROUP B   |         3 |        0|

I have tried the following:

SELECT g.name, COUNT(x.id_player) as num_players, x.flg_admin 
FROM player_group x 
JOIN group g ON x.id_group=g.id 
WHERE id_group IN ( 
    SELECT
    id_group 
    FROM player_group 
    WHERE id_player=27) 
GROUP BY id_group;

However with this query i get the following result:

|group_name|no. members|flg_admin|
----------------------------------
|GROUP A   |         2 |        1|
|GROUP B   |         3 |        1|

The flg_admin is set as 1 for GROUP B but as you can see in the images above, this player is not an admin of GROUP B. I have been trying to get this right for hours to no avail and i have exhausted my options, any help with this query would be greatly appreciated.

Upvotes: 0

Views: 3257

Answers (3)

Junjie
Junjie

Reputation: 521

My alternative solution using subquery:

select tgrp.name, mgrp.members, pgrp.flg_admin
from player_group pgrp
  join `group` tgrp on pgrp.id_group=tgrp.id
  join (
    select id_group, count(*) members
    from player_group
    group by id_group
  ) mgrp on tgrp.id=mgrp.id_group
where pgrp.id_player=27;

Upvotes: 0

Barmar
Barmar

Reputation: 781004

x.flg_admin is not taken from the row of the table for the selected player, it's coming from an arbitrary row in the group.

You need to join with the player_group table twice so you can get this column from the correct row.

SELECT g.name, COUNT(*) as num_players, y.flg_admin 
FROM player_group x 
JOIN `group` g ON x.id_group=g.id 
JOIN player_group y ON y.id_group = x.id_group
WHERE y.id_player = 27
GROUP BY x.id_group

DEMO

BTW, you shouldn't use group as a table or column name, because it's a reserved keyword.

Upvotes: 2

Shawn
Shawn

Reputation: 52364

Something like:

SELECT g.name AS group_name
    , (SELECT count(*) FROM player_group AS px WHERE px.id_group = pg.id_group) AS "no. members"
    , flg_admin
FROM players AS p
JOIN player_group AS pg on p.id = pg.id_player
JOIN "group" AS g on pg.id_group = g.id
WHERE p.username = 'player1';

maybe.

Upvotes: 0

Related Questions