You want the data to be added to the query through the left outer join, so as NOT to repeat

Question

I have a database structure (ER chart below) that has three levels of hierarchical data and a fourth level of optional data.

If I write a request to get de-normalized data of three levels - level 1 to level 3 with data samples for the three tables shown below:

When requested, this data layout is very simple and as expected, as shown below:

When I run the following query, I get the following output (And I tried various combinations, dialing the L1-L4 set and moving one L4 as another request, and then connecting the L1-L4 set, etc.) - again this is on the expected lines.

SELECT        [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, Comment.CommentId, Comment.CommentText, ManagementResponse.ManagementResponseId, 
                         ManagementResponse.ManagementResponseTest
FROM            Category INNER JOIN
                         [Group] ON Category.GroupId = [Group].GroupId INNER JOIN
                         RLI ON Category.CategoryId = RLI.CategoryId LEFT OUTER JOIN
                         ManagementResponse ON RLI.RLIId = ManagementResponse.RLIId LEFT OUTER JOIN
                         Comment ON RLI.RLIId = Comment.RLIId

- , , ( , 4 , 4 ):

Answer 1

:

WITH CommentAndResponse AS (

SELECT Comment.CommentId,
         Comment.CommentText, 
         ManagementResponse.ManagementResponseId, 
         ManagementResponse.ManagementResponseTest,
    COALESCE(Comment.RLIId, ManagementResponse.RLIId) AS RLIId
 FROM (
    (SELECT Comment.CommentId, 
            Comment.CommentText,
            Comment.RLIId,
            ROW_NUMBER() OVER (PARTITION BY Comment.RLIId ORDER BY Comment.CommentId) AS CommentRowNumber
    FROM Comment) AS Comment
    FULL JOIN
    (SELECT  ManagementResponse.ManagementResponseId, 
            ManagementResponse.ManagementResponseTest,
            ManagementResponse.RLIId,
            ROW_NUMBER() OVER (PARTITION BY ManagementResponse.RLIId ORDER BY ManagementResponse.ManagementResponseId) AS ManagementResponseRowNumber   
    FROM ManagementResponse) AS ManagementResponse
    ON Comment.CommentRowNumber = ManagementResponse.ManagementResponseRowNumber AND Comment.RLIId = ManagementResponse.RLIId ) 
    )

SELECT          [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, CommentAndResponse.CommentId, CommentAndResponse.CommentText, CommentAndResponse.ManagementResponseId, CommentAndResponse.ManagementResponseTest
FROM            [Category]
INNER JOIN      [Group] ON Category.GroupId = [Group].GroupId 
INNER JOIN      [RLI] ON Category.CategoryId = RLI.CategoryId 
LEFT OUTER JOIN [CommentAndResponse] ON RLI.RLIId = CommentAndResponse.RLIId 

Answer 2

, Comment.CommentId ManagementResponse.ManagementResponseId null. JOIN WHERE

SELECT          [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, Comment.CommentId, Comment.CommentText, ManagementResponse.ManagementResponseId, 
                         ManagementResponse.ManagementResponseTest
FROM            [Category]
INNER JOIN      [Group] ON Category.GroupId = [Group].GroupId 
INNER JOIN      [RLI] ON Category.CategoryId = RLI.CategoryId 
LEFT OUTER JOIN [ManagementResponse] ON RLI.RLIId = ManagementResponse.RLIId 
LEFT OUTER JOIN [Comment] ON RLI.RLIId = Comment.RLIId
WHERE           ManagementResponse.ManagementResponseId = Comment.CommentId OR ManagementResponse.ManagementResponseId IS NULL OR Comment.CommentId IS NULL

, - , . , , , , . , Comment ManagementResponse , RLIId, -

WITH CommentAndResponse AS (
    SELECT Comment.CommentId, Comment.CommentText, ManagementResponse.ManagementResponseId, ManagementResponse.ManagementResponseTest,
        COALESCE(Comment.RLIId, ManagementResponse.RLIId) AS RLIId,
        ROW_NUMBER() OVER (ORDER BY Comment.CommentId, ManagementResponse.ManagementResponseId, PARTITION BY Comment.RLIId, ManagementResponse.RLIId) AS rn
    FROM Comment
    FULL JOIN ManagementResponse ON Comment.RLIId = ManagementResponse.RLIId)
SELECT          [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, CommentAndResponse.CommentId, CommentAndResponse.CommentText, CommentAndResponse.ManagementResponseId, CommentAndResponse.ManagementResponseTest
FROM            [Category]
INNER JOIN      [Group] ON Category.GroupId = [Group].GroupId 
INNER JOIN      [RLI] ON Category.CategoryId = RLI.CategoryId 
LEFT OUTER JOIN [CommentAndResponse] ON RLI.RLIId = CommentAndResponse.RLIId AND CommentAndResponse.rn = 1