CODEWITHSUNDEEP
sqlsql-server-2012
Robert
Robert

Reputation: 1726

Return both parent and child data from tables

I'm using SQL Server 2012 and have 3 tables that I'm trying to pull data from: Role, DependentRoles, and ActiveRole:

tables

I need to return data for a given Role and data for any DependentRole for that role as well. For instance, If the Role table has ids 1-15 and the DependentRoles table is populated like so:

enter image description here

we find that for the RoleId of 2, it has 2 DependentRoles; 4 & 5. So if I want to find all the Roles that are assigned to a given role, my sql looks like this:

SELECT dr.*
FROM DependentRoles dr
INNER JOIN [Role] r ON dr.ChildRoleId = r.RoleId
INNER JOIN ActiveRole ar ON r.RoleId = ar.RoleId
WHERE ar.RoleId = 4

which produces this:

enter image description here

which tells me that Roles 5, 7, 9, 2, 10, 11, & 12 all depend on Role 4. What I now need is to return the RoleName for the ParentRoleId and ChildRoleId. I can't just add r.RoleName to the sql above because it returns just the Child RoleName, I need both. I'd like to return DependentRoleId, ParentRoleId, ParentRoleName, ChildRoleId, ChildRoleName:

2    5   Role-E    4    Role-D 
3    7   Role-G    4    Role-D
5    9   Role-I    4    Role-D
8    2   Role-B    4    Role-D

I tried joining on the Role table again but to the ParentRoleId but I'm not getting any results for ParentRoleName:

SELECT dr.DependentRoleId, dr.ParentRoleId, r2.RoleName AS 'ParentRoleName', r.RoleId, r.RoleName AS 'ChildRoleName'
FROM DependentRoles dr
INNER JOIN [Role] r ON dr.ChildRoleId = r.RoleId
LEFT OUTER JOIN [Role] r2 ON dr.ParentRoleId = r.RoleId
INNER JOIN ActiveRole ar ON r.RoleId = ar.RoleId
WHERE ar.RoleId = 4

enter image description here

Any ideas on how I can get my desired results? I must add that I will be translating this into linq statements so I'm trying to avoid CTE and other methods that I cannot convert.

Upvotes: 1

Views: 48

Answers (1)

Fleury26
Fleury26

Reputation: 585

In your OUTER JOIN your ON is on r and not r2. Furthermore, if you always have a parent role, you should use INNER JOIN so something like this should work :

SELECT dr.DependentRoleId, dr.ParentRoleId AS 'ParentRoleID', 
r2.RoleName AS 'ParentRoleName', dr.ChildRoleId AS 'ChildRoleID' , r.RoleName AS 'ChildRoleName'
FROM DependentRoles dr
INNER JOIN [Role] r ON dr.ChildRoleId = r.RoleId
INNER JOIN [Role] r2 ON dr.ParentRoleId = r2.RoleId
INNER JOIN ActiveRole ar ON r.RoleId = ar.RoleId
WHERE ar.RoleId = 4

Upvotes: 2

Related Questions