Joining on empty rows I've got two tables.

| tbAccountValues |
+-----------------+
| Branch |  Month  | Values |
+--------+---------+--------+
| 0001   | January | 1000   |
+--------+---------+--------+

| tbMonths |
+----------+
| Code |  Description  | 
+------+---------------+
| 01   | January       | 
| 02   | February      |
| 03   | March         |
... up to December
+------+---------------+


What I want to display is

| Branch |  Month   | Values |
+--------+----------+--------+
| 0001   | January  | 1000   |
| 0001   | February | 0      |
| 0001   | March    | 0      |
| 0001   | April    | 0      |
| 0001   | May      | 0      |
... up to December
+--------+----------+--------+


What I have right now is

SELECT  a.Branch, b.Description, a.Value
FROM    tbAccountValues a
        LEFT OUTER JOIN tbMonths ON 1 = 1;


But it shows only the same values 12 times.

| Branch |  Month   | Values |
+--------+----------+--------+
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
| 0001   | January  | 1000   |
+--------+----------+--------+
SELECT ta.Branch, tm.Description, COALESCE(ta.Values, 0) Values
FROM tbMonths tm
LEFT JOIN tbAccountValues ta ON ta.Month = tm.Description  


The reason this works is that a LEFT JOIN requires two columns that form a relationship (be that PK/FK or otherwise). These columns have to contain identical values to JOIN the rows together.

In your example the tbAccountValues.Month and tbMonths.Description columns seem to contain the same values, so a JOIN can be made on those columns.


  It almost works though the problem is the branch column becomes null for those with 0 values


WITH tbBranches AS (SELECT DISTINCT Branch FROM tbAccountValues),
tbBranchesMonths AS (SELECT tB.Branch, tM.Description FROM tbBranches tB, tbMonths tM)
SELECT tBM.Branch, tBM.Description, COALESCE(tA.[Values], 0) [Values]
FROM tbBranchesMonths tBM
LEFT JOIN tbAccountValues tA ON tA.Month = tBM.Description  
                            AND tA.Branch = tBM.Branch


fiddle