I have 3 tables below. I am trying to compare the roles of each user. That is I want to show that John has 2 roles while Jane has 1. Currently I am only showing the common roles between them.
Sec_role (PK R_KEY, rname)Role_User(PK R_KEY, PK USER_KEY)User(PK USER_KEY, Name)Sample data:
Sec_Role Role_User User
R_Key|rname| R_Key|User_Key User_KEY|NAME
1 |Analyst 1 |1 1 |John
2 |Sysadmin 2 |1 2 |Jane
2 |2
I am trying to get
User | Role | User2 | Role2
John | Analyst | Jane | (Empty because she isn't an analyst)
John | sysadmin| Jane |Sysadmin
SELECT U.Name AS User1,
U2.Name AS User2,
R.rname AS Role1
R2.Rname AS Role2
FROM Sec_Role AS R
LEFT OUTER JOIN Role_User AS RU
ON R.R_Key=RU.R_Key
LEFT OUTER JOIN User AS U
ON U.User_Key=RU.User_Key
LEFT OUTER JOIN Sec_Role AS R2
ON R2.R_Key=R.R_Key <---(I think this is the issue here)
LEFT OUTER JOIN Role_User AS RU2
ON R2.R_Key=RU2.R_Key
LEFT OUTER JOIN User AS U2
ON U2.User_Key=RU2.User_Key
WHERE U.Name ='John'
AND U2.Name='Jane'
Currently I am getting the Intersection of these two Users.
User | Role | User2 | Role2
John | sysadmin| Jane |Sysadmin
WHERE U.Name ='John'
OR U2.Name='Jane'
note an OR not an AND
why the 4 columns
SELECT U.Name AS User,
R.rname AS Role
FROM Sec_Role AS R
LEFT OUTER JOIN Role_User AS RU
ON R.R_Key=RU.R_Key
LEFT OUTER JOIN User AS U
ON U.User_Key=RU.User_Key
where uName in ('','')
order by 1, 2
Try this
SELECT U.Name AS User1,
U2.Name AS User2,
R.rname AS Role1
R2.Rname AS Role2
FROM Sec_Role AS R
LEFT OUTER JOIN Role_User AS RU
ON R.R_Key=RU.R_Key
LEFT OUTER JOIN User AS U
ON U.User_Key=RU.User_Key
and U.Name ='John'
LEFT OUTER JOIN Sec_Role AS R2
ON R2.R_Key=R.R_Key <---(I think this is the issue here)
LEFT OUTER JOIN Role_User AS RU2
ON R2.R_Key=RU2.R_Key
LEFT OUTER JOIN User AS U2
ON U2.User_Key=RU2.User_Key
AND U2.Name='Jane'