complex MySQL query with multiple tables (read for more details)

Question

I have 4 tables: Teams, Users, UserTeams and Permissions:

Users:

UserID | Name
------------------------
1      | John
2      | Mike
3      | Tom

Teams:

TeamID | Name
-------------------------------
1      | FC Barcelona
2      | Real Madrid
3      | Manchester United
4      | Liverpool

Permissions:

PermissionsID | Name
-------------------------------
1             | Read Comments
2             | Write Comments
3             | Send Email
4             | Admin

UserTeams:

ID | UserID | TeamID | PermissionID
--------------------------------------------
1  | 1      | 1      | 1
2  | 1      | 1      | 2
3  | 1      | 1      | 4
4  | 1      | 2      | 1
4  | 1      | 4      | 3

-

UserID = 1 (we know this before a query)

I want to make the query to get something like this:

Permission.Name | FC Barcelona | Real Madrid | Manchester United | Liverpool | etc...
----------------------------------------------------------------------------
Read Comments   | (something)  | (something) | NULL              | NULL
Write Comments  | (something)  | NULL        | NULL              | NULL
Send Email      | NULL         | NULL        | NULL              | (something)
Admin           | (something)  | NULL        | NULL              | NULL

Number of teams are not limited.

Any ideas? I dont't mind if there are multiple queries...

Thanks in advance!

SOLUTION

First select teams only:

SELECT TeamID, name FROM Teams

Then create a new query with Teams data:

SELECT     
Permissions.name as 'permissionName', 

<Loop this line with the previous query>
    MAX(CASE WHEN Teams.name = 'FC Barcelona' THEN Teams.name ELSE NULL END) AS 'FC Barcelona'
<End loop>

FROM Permissions
LEFT JOIN UserTeams ON UserTeams.PermissionID = Permissions.PermissionID AND UserTeams.UserID = '1' 
LEFT JOIN Teams ON Teams.TeamID = UserTeams.TeamID   
GROUP BY Permissions.name

Now we have exactly the result we wanted.

Read up on Pivot Tables: http://en.wikibooks.org/wiki/MySQL/Pivot_table — Matt Grande, Feb 02 '12 at 17:15

score 1 · Answer 1 · answered Feb 02 '12 at 17:16

1

Well i think your tables are incomplete.. where will be that '(something)' will be stored

answered Feb 02 '12 at 17:16

Dhiraj Valechha

161
1
2
9

if there are permissions for the row/column the (something)value can be anything. if there are no permissions then the row/column is NULL – Hese Feb 02 '12 at 17:29

score 0 · Answer 2 · answered Feb 02 '12 at 17:37

The number of columns in a query result is not variable, you will always get the same number of columns. So I would recommend a query result like this:

Permission     | Team                |  User_name |  User_id
-----------------------------------------------------------------
Read Comments  | FC Barcelona        | John       | 1
Write Comments | Real Madrid         | John       | 1
Send Email     | Manchester United   | Mike       | 2
Admin          | ...(unlimited)      | Tom        | 2

(of course with correct values ;) )

The SQL query for this would be:

SELECT 
    p.Name AS Permission,
    u.Name AS User_name,
    t.Name AS Team,
    u.id AS User_id
FROM
    UserTeams AS ut
INNER JOIN
    Users AS u ON (ut.UserId = u.UserId)
INNER JOIN
    Teams AS t ON (ut.TeamId = t.TeamId)
INNER JOIN
    Permissions AS p ON (ut.PermissionId = p.PermissionsId)
WHERE
    u.UserID = :userId

Hmm... how this would help if I need to get list of permissions for every team for selected user (UserID = 1)? — Hese, Feb 02 '12 at 18:15
`permissions = array()` `foreach($result as $row){` ` $permissions[$row['User_id']][$row['Team']][$row['Permission']]=$row['Persmission']; `}` now you have an array and you can iterate with foreach... It's the best solution because you just need one query — phil-opp, Feb 17 '12 at 23:43

complex MySQL query with multiple tables (read for more details)

-

-

2 Answers2