t-clausen.dk 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

Answer
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
Comments