SQL query from multiple tables with multiple JOINs

Question

I have the following database where user account details are stored in the Users table, organisation details are linked to users through the 'UsersOrganisation_Map' junction table and user permissions are linked to users through the UsersSiteRoles_Map junction table. The database is MySQL

What I'm struggling with is how to get data from both the roles and org tables in a single query and have the results returned as if they're from a single table.

Here's an example script (that doesn't work) that will hopefully describe what I'm trying to achieve.

> select * from UsersOrganisation_Map, UsersSiteRoles_Map join Users on
> UsersOrganisationMap.userID = Users.userID join Organisation on
> UsersOrganisationMap.organisationID = Organisation.organisationID
> where userEmail = '[email protected]' AND userPassword = 'test' AND
> accountActive = 'YES' join Users on UsersSiteRoles_Map.userID =
> Users.userID join SiteRoles on UsersSiteRoles_Map.roleID =
> SiteRoles.roleID

enter image description here

Can you tell us, what exactly does not work? And could you give us a an example of your desired output? — d4Rk
– d4Rk, Commented Jun 6, 2015 at 10:00

Orel Eraki · Accepted Answer · 2015-06-06 10:01:31Z

2

This should be something similar to:

SELECT
    u.*,
    usr.*,
    sr.*,
    uom.*,
    o.*
FROM
    Users as u
JOIN UsersSiteRoles_Map as usr
    ON u.userID = usr.UserId
JOIN SiteRoles as sr
    ON UsersSiteRoles_Map.roleID = sr.roleID
JOIN UsersOrganisation_Map as uom
    ON u.userID = uom.userID
JOIN Organisation as o
    ON o.organisationID = uom.Id
WHERE
    u.userEmail = '[email protected]'
    AND u.userPassword = 'test'
    AND u.accountActive = 'YES'

answered Jun 6, 2015 at 10:01

Orel Eraki

12.2k4 gold badges31 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

glefait · Accepted Answer · 2015-06-06 10:06:15Z

0

You just need to join all the five tables together like :

SELECT *
FROM Users u
-- join to get roles
JOIN UsersSiteRoles_Map ur ON u.id=ur.user_id
JOIN SiteRoles r ON um.role_id=r.id
-- join to get organisation
JOIN UsersOrganisation_Map uo ON u.id=uo.user_id
JOIN Organisation o ON uo.organisation_id=o.id
-- filter results
WHERE u.email=admin ...

answered Jun 6, 2015 at 10:06

glefait

1,6911 gold badge13 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL query from multiple tables with multiple JOINs

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related