SQL join with union-CodePudding

Cant understand what is the problem the union query works just fine if run it without join but i need to use it inside left join. And when I try it shows me the error that says that column DisplayName used more than once. What im doing wrong?

    LEFT JOIN (
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName 
    FROM
        cm.Projects p
        LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
        JOIN DBL.[Bl_2b987e3e-4939-e711-80c8-000d3af7ae73] b ON pvb.Id = b.ProjectVisualBlockId
        LEFT JOIN cm.Participants [User] ON [User].Id= b.[4a789414-4a39-e711-80c8-000d3af7ae73]
        LEFT JOIN cm.Participants [User_A] ON [User_A].Id= b.[4c789414-4a39-e711-80c8-000d3af7ae73] 
    WHERE
        p.ProjectTypeId= '526ba5f2-4c39-e711-80c8-000d3af7ae73' 
UNION
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName 
    FROM
        cm.Projects p
        LEFT JOIN (
        SELECT
            pp.Id,
            d.DisplayName 
        FROM
            cm.Projects pp
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= pp.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_F1CC2727-45C5-E711-80C2-0CC47A966E25] ln ON ln.ProjectLineId = pl.Id 
            AND ln.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants d ON d.Id= ln.[F2CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) dlz ON dlz.Id= p.Id
        LEFT JOIN (
        SELECT
            p.Id,
            [User].DisplayName 
        FROM
            cm.Projects p
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_A25439AA-B51B-E811-80C2-0CC47A966E25] l ON l.ProjectLineId = pl.Id 
            AND l.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants [User] ON [User].Id= l.[F0CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) zvl ON zvl.Id= p.Id 
    WHERE
    p.ProjectTypeId= 'b498aad9-3ec5-e711-80c2-0cc47a966e25' 
    ) xavdol ON xavdol.Id= p.Id

CodePudding user response：

you have used column DisplayName twice, change to this:

LEFT JOIN (
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName as DisplayName2
    FROM
        cm.Projects p
        LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
        JOIN DBL.[Bl_2b987e3e-4939-e711-80c8-000d3af7ae73] b ON pvb.Id = b.ProjectVisualBlockId
        LEFT JOIN cm.Participants [User] ON [User].Id= b.[4a789414-4a39-e711-80c8-000d3af7ae73]
        LEFT JOIN cm.Participants [User_A] ON [User_A].Id= b.[4c789414-4a39-e711-80c8-000d3af7ae73] 
    WHERE
        p.ProjectTypeId= '526ba5f2-4c39-e711-80c8-000d3af7ae73' 
UNION
    SELECT
        p.Id,
        zvl.DisplayName,
        dlz.DisplayName  as DisplayName2
    FROM
        cm.Projects p
        LEFT JOIN (
        SELECT
            pp.Id,
            d.DisplayName 
        FROM
            cm.Projects pp
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= pp.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_F1CC2727-45C5-E711-80C2-0CC47A966E25] ln ON ln.ProjectLineId = pl.Id 
            AND ln.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants d ON d.Id= ln.[F2CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) dlz ON dlz.Id= p.Id
        LEFT JOIN (
        SELECT
            p.Id,
            [User].DisplayName 
        FROM
            cm.Projects p
            LEFT JOIN cm.ProjectVisualBlocks pvb ON pvb.ProjectId= p.Id
            JOIN cm.ProjectLines pl ON pvb.Id = pl.ProjectVisualBlockId
            JOIN DBL.[Ln_A25439AA-B51B-E811-80C2-0CC47A966E25] l ON l.ProjectLineId = pl.Id 
            AND l.ProjectVisualBlockId = pvb.Id
            JOIN cm.Participants [User] ON [User].Id= l.[F0CC2727-45C5-E711-80C2-0CC47A966E25] 
        ) zvl ON zvl.Id= p.Id 
    WHERE
    p.ProjectTypeId= 'b498aad9-3ec5-e711-80c2-0cc47a966e25' 
    ) xavdol ON xavdol.Id= p.Id
sql
sql-server