SQL Server Filling in Missing Record

Viewed 34

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
itemId funcId reviewStatus funcId funcName
1 1 2 1 Marketing
1 2 2 2 Safety
1 3 2 3 Provisioning
2 1 2 1 Marketing
2 2 2 2 Safety
2 3 2 3 Provisioning
2 4 2 4 Boss

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

itemId funcId reviewStatus funcId funcName
1 1 2 1 Marketing
1 2 2 2 Safety
1 3 2 3 Provisioning
1 4 0 4 Boss
2 1 2 1 Marketing
2 2 2 2 Safety
2 3 2 3 Provisioning
2 4 2 4 Boss
1 Answers

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
Related