CODEWITHSUNDEEP
mysqlsql
user1542125
user1542125

Reputation: 615

SQL query from multiple tables with multiple JOINs

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

Upvotes: 1

Views: 49

Answers (2)

glefait
glefait

Reputation: 1691

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 ...

Upvotes: 0

Orel Eraki
Orel Eraki

Reputation: 12196

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'

Upvotes: 2

Related Questions