t-clausen.dk - 5 months ago 10
SQL Question

# Join on each possible combination for each group

I have 2 tables

``````T1
order1      grp
----------- -----------
1           1
2           1
3           1
4           1
1           2
2           2
3           2

T2
order1      count1      abbrev grp
----------- ----------- ------ -----------
2           5           A      1
1           17          B      2
2           11          B      2
``````

The tables are joined this way:

``````SELECT
t2.grp, t2.order1, t2.count1, t2.abbrev
FROM t2
JOIN t1
ON
t1.grp = t2.grp and
t1.order1 = t2.order1
``````

Result:

``````grp         order1      count1      abbrev
----------- ----------- ----------- ------
1           2           5           A
2           1           17          B
2           2           11          B
``````

Problem is that i need a row for each possible combination for each group:

``````grp         order1      count1      abbrev
----------- ----------- ----------- ------
1           1           0           A
1           2           5           A
1           3           0           A
1           4           0           A
2           1           17          B
2           2           11          B
2           3           0           B
``````

``````SELECT
cube.grp,
cube.order,
ISNULL(data.count, 0),
chars.abbrev
FROM
@t1   AS cube
INNER JOIN
(SELECT grp, abbrev FROM @t2 GROUP BY grp, abbrev) AS chars
ON chars.grp = cube.grp
LEFT JOIN
@t2   AS data
ON  data.grp    = cube.grp
AND data.order  = cube.order
AND data.abbrev = chars.abbrev
``````