CODEWITHSUNDEEP
sqljoin
KingleyMosso
KingleyMosso

Reputation: 47

Conditional SQL Join from several tables

I have three tables that are connected. I need to make a single SQL selection and I know I need to use join of some form but not quite how. I only want the objectID to be selected if the session for userID has access to that object´s area (user gets access if their company has access to the area) The tables I have are:

+----------+--------+ Objects
| objectID | areaID |
+----------+--------+
|        1 |      2 |
|        2 |      2 |
|        3 |      3 |
+----------+--------+
+-----------+--------+ Users
| companyID | userID |
+-----------+--------+
|         1 |      1 |
|         1 |      2 |
|         1 |      3 |
+-----------+--------+
+-----------+--------+ Access
| companyID | areaID |
+-----------+--------+
|         1 |      1 |
|         1 |      2 |
|         1 |      3 |
+-----------+--------+

Upvotes: 0

Views: 46

Answers (2)

India.Rocket
India.Rocket

Reputation: 1245

Selected UserID and corresponding areaIDs in inner query using companyID. Then using this, selected distinct ObjectID from Objects table using inner join again with table formed through inner query

Try this query:-

Select distinct a.objectID 
from
Objects a
inner join
(
Select a.*,b.areaID
from
Users a
inner join
Access b
on a.companyID=b.companyID
) b
on a.areaID=b.areaID
Where b.UserID= 1;

Upvotes: 1

nxl4
nxl4

Reputation: 734

If I understand you right, it seems like two INNER JOIN functions would get the results you're looking for:

WITH ONE AS (
    SELECT a.companyID, a.userID, b.areaID
    FROM Users a
    INNER JOIN Access b
    ON a.companyID = b.companyID
    )

SELECT a.companyID, a.userID, a.areaID, b.objectID
FROM ONE a
INNER JOIN Objects b
ON a.areaID = b.areaID;

EDIT: Reply to comment

If you want to limit the results to a specific userID value, you'll need a WHERE clause:

WITH ONE AS (
    SELECT a.companyID, a.userID, b.areaID
    FROM Users a
    INNER JOIN Access b
    ON a.companyID = b.companyID
    )

SELECT a.companyID, a.userID, a.areaID, b.objectID
FROM ONE a
INNER JOIN Objects b
ON a.areaID = b.areaID
WHERE a.userID = ${specific_user_id};

Upvotes: 0

Related Questions