Full Join on Group

Question

I'm facing a logic issue with my Query.

I have two tables Table1 and Table2, where Table1 consists of:

• value to be summed
• Id to be grouped by
• Code holds foreign-key to Table2

And Table2 consists of

• Code
• Des the text description of code

What I'm trying to do is, group by Table1.Id, full join on Table2.Code, but, for each resulting group, I want to show all the rows from Table2 for each group generated by the query.

Sample code:

SELECT
    Table2.Code, Table1.Id, Table2.DES, 
    SUM(Table1.Value) AS SUM_VAL
FROM 
(
    SELECT 'A' AS Code, 1 AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'A' AS Code, 2 AS Id, 20 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 1 AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 1 AS Id, 30 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 2 AS Id, 50 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 1 AS Id, 40 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 2 AS Id, 60 AS Value FROM DUAL UNION
    SELECT 'D' AS Code, 1 AS Id, 20 AS Value FROM DUAL

) Table1
FULL JOIN
(
    SELECT 'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'D' AS Code, 'This is D' AS DES FROM DUAL
) Table2
ON Table1.Code = Table2.Code
GROUP BY
    Table2.Code, Table1.Id, Table2.DES
ORDER BY
    Table2.Code, Table1.Id ASC

Result:

A   1   This is A   10
A   2   This is A   20
B   1   This is B   40
B   2   This is B   50
C   1   This is C   40
C   2   This is C   60
D   1   This is D   20

Required Result:

A   1   This is A   10
A   2   This is A   20
B   1   This is B   40
B   2   This is B   50
C   1   This is C   40
C   2   This is C   60
D   1   This is D   20
D   2   This is D   0    <- This is the target

Answer 1

You have somehow to show the value pair (D,2) eg. by making a code list with possible values and translating NULL to 0:

  SELECT code.code,
         code.id,
         des.des,
         NVL (SUM (val.value), 0) sum_val
    FROM (SELECT 'A' code, 1 id FROM DUAL
          UNION
          SELECT 'A', 2 FROM DUAL
          UNION
          SELECT 'B', 1 FROM DUAL
          UNION
          SELECT 'B', 2 FROM DUAL
          UNION
          SELECT 'C', 1 FROM DUAL
          UNION
          SELECT 'C', 2 FROM DUAL
          UNION
          SELECT 'D', 1 FROM DUAL
          UNION
          SELECT 'D', 2 FROM DUAL) code
         INNER JOIN (SELECT 'A' code, 'This is A' des FROM DUAL
                     UNION
                     SELECT 'B', 'This is B' FROM DUAL
                     UNION
                     SELECT 'C', 'This is C' FROM DUAL
                     UNION
                     SELECT 'D', 'This is D' FROM DUAL) des
            ON code.code = des.code
         LEFT OUTER JOIN (SELECT 'A' code, 1 id, 10 VALUE FROM DUAL
                          UNION ALL
                          SELECT 'A', 2, 20 FROM DUAL
                          UNION ALL
                          SELECT 'B', 1, 10 FROM DUAL
                          UNION ALL
                          SELECT 'B', 1, 30 FROM DUAL
                          UNION ALL
                          SELECT 'B', 2, 50 FROM DUAL
                          UNION ALL
                          SELECT 'C', 1, 40 FROM DUAL
                          UNION ALL
                          SELECT 'C', 2, 60 FROM DUAL
                          UNION ALL
                          SELECT 'D', 1, 20 FROM DUAL) val
            ON code.code = val.code AND code.id = val.id
GROUP BY code.code, code.id, des.des
ORDER BY code, id

UNION ALL is used in val because duplicates can occur.

No need for FULL OUTER JOIN.

Answer 2

If you want all the combinations of id and value, then use cross join to get the rows and a left join to bring in the rest of the values:

select t2.code, i.value, t2.desc, coalesce(cnt, 0) as cnt
from (select distinct id from table1) i cross join
     table2 t2 left join
     (select id, value, count(*) as cnt
      from table1
      group by id, value
     ) iv
     on iv.id = i.id and iv.code = t2.code

This should be much simpler than listing out all the combinations manually.

Answer 3

SELECT
    Table2.Code, Table2.NAT, Table2.DES, 
    SUM(Table1.Value) AS SUM_VAL
FROM 
(
    SELECT 'A' AS Code, 'QA' AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'A' AS Code, 'NQA' AS Id, 20 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'QA' AS Id, 10 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'QA' AS Id, 30 AS Value FROM DUAL UNION
    SELECT 'B' AS Code, 'NQA' AS Id, 50 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 'QA' AS Id, 40 AS Value FROM DUAL UNION
    SELECT 'C' AS Code, 'NQA' AS Id, 60 AS Value FROM DUAL UNION
    SELECT 'D' AS Code, 'QA' AS Id, 20 AS Value FROM DUAL

) Table1
FULL JOIN
(
    SELECT 'QA' NAT,'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'QA' NAT,'D' AS Code, 'This is D' AS DES FROM DUAL
    UNION
    SELECT 'NQA' NAT,'A' AS Code, 'This is A' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'B' AS Code, 'This is B' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'C' AS Code, 'This is C' AS DES FROM DUAL UNION
    SELECT 'NQA' NAT,'D' AS Code, 'This is D' AS DES FROM DUAL
) Table2
on TABLE2.NAT = TABLE1.ID
      AND Table2.Code= Table1.Code
GROUP BY
    Table2.Code, Table2.NAT, Table2.DES
ORDER BY
    Table2.Code, Table2.NAT ASC