I've tried searching Google and reviewing StackOverflow and have found similar issues but none that seem to have helped with my specific issue.

There are two tables involved -

#func that lists the business functions responsible for reviewing an item.

#review which captures review status (In Review = 1, Complete = 2) of an item by each function.

Below is simplified schema and data.

CREATE TABLE #func
(
    funcId int,
    funcName varchar(25)
)

INSERT INTO #func VALUES (1, 'Marketing');
INSERT INTO #func VALUES (2, 'Safety');
INSERT INTO #func VALUES (3, 'Provisioning');
INSERT INTO #func VALUES (4, 'Boss');

CREATE TABLE #review
(
    itemId int,
    funcId varchar(25),
    reviewStatus int
)

INSERT INTO #review VALUES (1, 1, 2);
INSERT INTO #review VALUES (1, 2, 2);
INSERT INTO #review VALUES (1, 3, 2);

INSERT INTO #review VALUES (2, 1, 2);
INSERT INTO #review VALUES (2, 2, 2);
INSERT INTO #review VALUES (2, 3, 2);
INSERT INTO #review VALUES (2, 4, 2);

Joining the two tables produces "incomplete" results "omitting" a row for the Boss business function for itemId = 1.

SELECT r.*, f.*
FROM #func f
LEFT JOIN #review r ON f.funcId = r.funcId
ORDER BY r.itemId, r.funcId

I understand why this is happening but how do I get it to return results like below?

CodePudding user response：

In cases like this you need to build a dataset of all possible combinations (CROSS JOIN) before left joining on your actual results. e.g.

SELECT i.itemId, f.funcName, COALESCE(r.reviewStatus,0) reviewStatus, f.funcName
FROM #func f
CROSS JOIN (
  SELECT itemId
  FROM #review
  GROUP BY itemId
) i
LEFT JOIN #review r ON f.funcId = r.funcId and r.itemId = i.itemId
ORDER BY i.itemId, f.funcId