How to left join on groups of data

group byjoin;

I would like to know how to left join columns for each group of data that is return in a set of data. The template needs to fill in the blanks for each group where there are missing records. Groups 1 and 2 are part of a single query and need to be part of the same result set.

TEMPLATE

Name          Num     
------------------------------------
Apples        10      
Bananas       20
Oranges       15
Pineapple     5
Grapes        30
Chips         50
Chocolate     6

GROUP 1

Name          Num         Group
------------------------------------
Grapes        3    --     1
Chips         17   --     1

GROUP 2

Name          Num         Group
------------------------------------
Bananas       30   --     2
Oranges       10   --     2

NEW GROUP 1 

Name          Num         Group
------------------------------------
Apples        10          1     <-- from template
Bananas       20          1     <-- from template
Oranges       15          1     <-- from template
Pineapple     5           1     <-- from template
Grapes        3           1     <-- same
Chips         17          1     <-- same
Chocolate     6           1     <-- from template

NEW GROUP 2

Name          Num         Group
------------------------------------
Apples        10          2     <-- from template
Bananas       30          2     <-- same
Oranges       10          2     <-- same
Pineapple     5           2     <-- from template
Grapes        30          2     <-- from template
Chips         50          2     <-- from template
Chocolate     6           2     <-- from template

Groups 1 and 2 are stored in different tables. The number of groups is not limited to two. The algorithm is, basically, "return template but overwrite template's num with group's num where it is given." The result needs to be a single result set.

Best Answer

New Group1:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '1'                   AS Group
FROM Template t
LEFT JOIN Group1 g ON t.Name=G.Name

New Group2:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   '2'                   AS Group
FROM Template t
LEFT JOIN Group2 g ON t.Name=G.Name

The ISNULL is comparable to the COALESCE function, but simpler to type, without breaking fingers ?

It needs to be a single result set since the numbers of groups is not limited to two

If your groups are in table Group, a single query would be:

SELECT
   Name                  AS Name,
   ISNULL(g.Num,t.Num)   AS Num,
   g.Group               AS Group
FROM Template t
LEFT JOIN Group g ON t.Name=G.Name
Related Question