Adding additional joins to SQL Union All

Question

My current SQL union all statement aganst a table called Person

ALTER PROCEDURE [dbo].[uspGetOrgChart]
@ContactID varchar(100)='NOE1WWD'
AS
BEGIN


--grab id of @contactid
DECLARE @Test varchar(36)
SELECT @Test = (SELECT ADID FROM Person c1 WHERE c1.ADID = @ContactID)


;WITH StaffTree AS 
( 
    SELECT  
        c.ADID, 
        c.First_Name, 
        c.Last_Name,
        c.Position_ID,
        c.Role_ID,
        c.Report_To_ADID, 
        c.Report_To_ADID as Manager_ID,
        cc.First_Name AS Manager_First_Name, 
        cc.Last_Name as Manager_Last_Name, 
        cc.Position_ID as Manager_Pos_ID,
        cc.Role_ID as Manager_Role_ID,
        cc.First_name + ' ' + cc.Last_name AS [ReportsTo], 
        c.First_Name + ' ' + c.Last_Name as EmployeeName,  
        1 AS LevelOf 
        FROM Person                  c 
            LEFT OUTER JOIN Person  cc ON c.Report_To_ADID = cc.ADID 
        WHERE c.ADID=@Test OR (@Test IS NULL AND c.Report_To_ADID IS NULL)

    UNION ALL 
        SELECT  
        s.ADID, 
        s.First_Name, 
        s.Last_Name, 
        s.Position_ID,
        s.Role_ID,
        s.Report_To_ADID, 
        t.ADID, 
        t.First_Name, 
        t.Last_Name, 
        t.Position_ID,
        t.Role_ID,
        t.First_Name + ' ' + t.Last_Name, 
        s.First_Name + ' ' + s.Last_Name,
        t.LevelOf+1 
        FROM StaffTree            t 
            INNER JOIN Person  s ON t.ADID=s.Report_To_ADID 
    WHERE s.Report_To_ADID=@Test OR @Test IS NULL OR t.LevelOf>1 
)
SELECT * FROM StaffTree 

END

I have two other tables called Position and Role

Position_ID Position_Detail

Role_ID Role_Detail

How do I add a join to this statement in order to return the Position_Detail and Role_Detail

Can you provide table schemas of the tables in question, as well as a printout of the "expected" result? — Martin, Aug 28 '18 at 06:24

score 0 · Answer 1 · answered Aug 28 '18 at 06:45

just use simple join with your sub-query

ALTER PROCEDURE [dbo].[uspGetOrgChart]
@ContactID varchar(100)='NOE1WWD'
AS
BEGIN


--grab id of @contactid
DECLARE @Test varchar(36)
SELECT @Test = (SELECT ADID FROM Person c1 WHERE c1.ADID = @ContactID)

;WITH StaffTree AS 
( 
    SELECT  
        c.ADID, 
        c.First_Name, 
        c.Last_Name,
        c.Position_ID,
        c.Role_ID,
        c.Report_To_ADID, 
        c.Report_To_ADID as Manager_ID,
        cc.First_Name AS Manager_First_Name, 
        cc.Last_Name as Manager_Last_Name, 
        cc.Position_ID as Manager_Pos_ID,
        cc.Role_ID as Manager_Role_ID,
        cc.First_name + ' ' + cc.Last_name AS [ReportsTo], 
        c.First_Name + ' ' + c.Last_Name as EmployeeName,  
        1 AS LevelOf 
        FROM Person                  c 
            LEFT OUTER JOIN Person  cc ON c.Report_To_ADID = cc.ADID 
        WHERE c.ADID=@Test OR (@Test IS NULL AND c.Report_To_ADID IS NULL)

    UNION ALL 
        SELECT  
        s.ADID, 
        s.First_Name, 
        s.Last_Name, 
        s.Position_ID,
        s.Role_ID,
        s.Report_To_ADID, 
        t.ADID, 
        t.First_Name, 
        t.Last_Name, 
        t.Position_ID,
        t.Role_ID,
        t.First_Name + ' ' + t.Last_Name, 
        s.First_Name + ' ' + s.Last_Name,
        t.LevelOf+1 
        FROM StaffTree            t 
            INNER JOIN Person  s ON t.ADID=s.Report_To_ADID 
    WHERE s.Report_To_ADID=@Test OR @Test IS NULL OR t.LevelOf>1 
)
SELECT s.*,p.Position_Detail,r.Role_Detail FROM StaffTree s inner join Position p on s.Position_ID=p.Position_ID
inner join Role r on s.Role_ID=r.Role_ID
  END

Adding additional joins to SQL Union All

1 Answers1