Sunday, August 26, 2012

multiple retaltional query using 4 table mysql




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 | 3 | 1 | 2
3 | 2 | 1 | 4
4 | 4 | 2 | 1
4 | 1 | 4 | 3
SELECT
p.Name AS Permission,
u.pseudo 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.id)
INNER JOIN
teams AS t ON (ut.TeamId = t.TeamId)
INNER JOIN
permissions AS p ON (ut.PermissionId = p.permissionsId)
WHERE
u.id= ut.userId
-------------------------------------------------------------
if you helpful my code please donate some few amount to
developing and free to post.
-------------------------------------------------------------

No comments:

Post a Comment