0

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.

  • 1
    @jwize I've added your comment to Luuk into the question. You can roll this back if I have miss-interpreted your intentions. If you have further clarifications please edit them into the original question directly. Specifically, if you want a single result set please edit the example output to show it as a single output table. – Michael Green Jul 14 '19 at 9:16
2

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
  • It needs to be a single result set since the numbers of groups is not limited to two – jwize Jul 13 '19 at 18:01
  • This is not a solution, but thanks anyways! – jwize Jul 13 '19 at 23:50
  • Added query with one result set. – Luuk Jul 14 '19 at 8:10
  • On which DBMS did you run this because '1' as Group AS Group looks suspect to me? – Michael Green Jul 14 '19 at 9:08
  • The OP stated (in a comment) that the groups "are not stored in the same table." You may want to suggest a solution with a UNION ALL component? – Michael Green Jul 14 '19 at 9:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.