In SQL Server I have Table1 with rows as follows
| Col 1 | Col 2 |
|---|---|
| A | Andy |
| B | Becky |
| C | Carol |
In SQL Server I have Table1 with rows as follows
| Col 1 | Col 2 |
|---|---|
| A | Andy |
| B | Becky |
| C | Carol |
In Table2 I have following rows
| Col 1 | Col 2 | Col 3 |
|---|---|---|
| A | Low | 3 |
| A | Medium | 2 |
| A | High | 5 |
| B | High | 7 |
| C | Low | 1 |
| C | High | 12 |
Using a join I want to out rows for all 3 values (Low, Medium, High) even if its missing from Table2
So essentially I want to get
| Col 1 | Col 2 | Col 3 |
|---|---|---|
| A | Low | 3 |
| A | Medium | 2 |
| A | High | 5 |
| B | Low | NULL |
| B | Medium | NULL |
| B | High | 7 |
| C | Low | 1 |
| C | High | 12 |
| C | Medium | NULL |
How can I write my join to return fixed rows even when missing in Table2?
Share Improve this question edited yesterday Dale K 27.3k15 gold badges57 silver badges83 bronze badges asked yesterday JakeUTJakeUT 5371 gold badge13 silver badges26 bronze badges 3- 1 What have you tried? Where did you get stuck? hint "Cross join". – Dale K Commented yesterday
- Although where should the list of Low, Medium, High come from? The only way to can be sure to show one of each is if you can build a list of all options somehow. Hopefully that enum us stored in another database table. – Dale K Commented yesterday
- Thanks! didnt think of the Cross join :) – JakeUT Commented yesterday
1 Answer
Reset to default 1You can cross-join Table1 with a constructed table of those three values (or a real table if you have one), then left join Table2.
SELECT
t1.Col1,
v.Col2,
t2.Col3
FROM Table1 t1
CROSS JOIN (VALUES
('Low'),
('Medium'),
('High')
) v(Col2)
LEFT JOIN Table2 t2
ON t2.Col1 = t1.Col1
AND t2.Col2 = v.Col2;
db<>fiddle