MySQL UNION ALL (Full Join) with conditional aggregation

Question

I have three tables:

CREATE TABLE `Agreement` (
  `AID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FLAGS` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`AID`)
);

CREATE TABLE `Assessment` (
  `ASMID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AID` bigint(20) NOT NULL DEFAULT '0',
  `Amount` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Description` text,
  PRIMARY KEY (`ASMID`)
);

CREATE TABLE `Payment` (
  `RID` bigint(20) NOT NULL AUTO_INCREMENT,
  `AID` bigint(20) NOT NULL DEFAULT '0',
  `ASMID` bigint(20) NOT NULL DEFAULT '0',
  `Amount` decimal(19,4) NOT NULL DEFAULT '0.0000',
  `Description` text,
  PRIMARY KEY (`RID`)
);

And I'm inserting one Agreement, three Assessment, five Payment rows mentioned as below:

INSERT INTO Agreement(FLAGS) VALUES(0);
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 1200, "Rent");
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 20, "Damage - car break");
INSERT INTO Assessment(AID, Amount, Description) VALUES (1, 500, "Damage - vehicle");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 1, 500, "Rent Fee");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 1, 600, "Rent Fee");
INSERT INTO Payment(AID, ASMID, Amount, Description) VALUES(1, 2, 20, "Damage Fee");
INSERT INTO Payment(AID, Amount, Description) VALUES(1, 600, "Deposit Fee");
INSERT INTO Payment(AID, Amount, Description) VALUES(1, 50, "Application Fee");

and When I see the data, so it should look like this:

mysql> SELECT * FROM Agreement;
+-----+-------+
| AID | FLAGS |
+-----+-------+
|   1 |     0 |
+-----+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Assessment;
+-------+-----+-----------+--------------------+
| ASMID | AID | Amount    | Description        |
+-------+-----+-----------+--------------------+
|     1 |   1 | 1200.0000 | Rent               |
|     2 |   1 |   20.0000 | Damage - car break |
|     3 |   1 |  500.0000 | Damage - vehicle   |
+-------+-----+-----------+--------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Payment;
+-----+-----+-------+----------+-----------------+
| RID | AID | ASMID | Amount   | Description     |
+-----+-----+-------+----------+-----------------+
|   1 |   1 |     1 | 500.0000 | Rent Fee        |
|   2 |   1 |     1 | 600.0000 | Rent Fee        |
|   3 |   1 |     2 |  20.0000 | Damage Fee      |
|   4 |   1 |     0 | 600.0000 | Deposit Fee     |
|   5 |   1 |     0 |  50.0000 | Application Fee |
+-----+-----+-------+----------+-----------------+
5 rows in set (0.00 sec)

So, any Agreement has multiple Assessments all need to be paid in near future. It may have multiple Payments which might be associated with Assessment (i.e., Rent Fee) or might be not (i.e., Application Fee).

Now, in reality, there are multiple Agreements which have multiple Assessments and multiple Payments.

Now I want the result that covers all of the rows from both tables Assessment and Payment associated with Agreement GROUPED BY first Agreement, second Assessment. Additionally, I need to aggregate AMOUNT as PaymentsApplied from the table Payment table for each Assessment so that we can compare it with Amount from the table Assessment as AmountDue. Plus if any Payment is not associated with any Assessment then don't do aggregation. The result would look like this:

+-----+-------+-----------+-----------------+--------------------+-----------------+
| AID | ASMID | AmountDue | PaymentsApplied |          ASM-Descr | PMT-Description |
+-----+-------+-----------+-----------------+--------------------+-----------------+
|   1 |     1 | 1200.0000 |       1100.0000 |               Rent |        Rent Fee |
|   1 |     2 |   20.0000 |         20.0000 | Damage - car break |     Damange Fee |
|   1 |     3 |  500.0000 |            NULL |   Damage - vehicle |            NULL |
|   1 |     0 |      NULL |        600.0000 |               NULL |     Deposit Fee |
|   1 |     0 |      NULL |         50.0000 |               NULL | Application Fee |
+-----+-------+-----------+-----------------+--------------------+-----------------+
5 Rows

I tried my best to explain the situation. Actually, in my application query has joins over 10 tables like Agreement!

Any Help would be most welcome!!

---

UPDATE 1

I've started from this query,

(SELECT DISTINCT
    Payment.RID, Payment.Amount as PaymentsApplied, Payment.ASMID as PMT_ASMID, null as AmountDue, null AS ASMID
FROM Payment
    LEFT JOIN Assessment ON Assessment.ASMID=Payment.ASMID)
UNION
(SELECT DISTINCT
    null, null, null, Assessment.Amount, Assessment.ASMID
FROM Assessment
    LEFT JOIN Payment ON Payment.ASMID=Assessment.ASMID)
ORDER BY ASMID, PMT_ASMID;

which gives me result,

+------+-----------------+-----------+-----------+-------+
| RID  | PaymentsApplied | PMT_ASMID | AmountDue | ASMID |
+------+-----------------+-----------+-----------+-------+
| NULL |            NULL |      NULL | 1200.0000 |     1 |
| NULL |            NULL |      NULL |   20.0000 |     2 |
| NULL |            NULL |      NULL |  500.0000 |     3 |
|    1 |        500.0000 |         1 |      NULL |  NULL |
|    2 |        600.0000 |         1 |      NULL |  NULL |
|    3 |         20.0000 |         2 |      NULL |  NULL |
|    4 |        600.0000 |         0 |      NULL |  NULL |
|    5 |         50.0000 |         0 |      NULL |  NULL |
+------+-----------------+-----------+-----------+-------+
8 rows in set (0.01 sec)

Now, from this point, IDK how to aggregate Payment rows by Assessment ID (ASMID) and do join with Agreement table also?

---

UPDATE 2

I've made sqlfiddle link just in case someone wants to try it.

I've added conditional aggregation in my query,

(SELECT DISTINCT
    null as AmountDue,
    null AS ASMID,
    null as ASM_Descr,
    Payment.Description as PMT_Descr,
    (CASE WHEN Payment.ASMID > 0 THEN SUM(Payment.Amount) ELSE Payment.Amount END) as PaymentsApplied,
    (CASE WHEN Payment.ASMID > 0 THEN GROUP_CONCAT(Payment.RID) ELSE Payment.RID END) as PaymentList,
    Payment.ASMID as PMT_ASMID
FROM Payment
    LEFT JOIN Assessment ON Assessment.ASMID=Payment.ASMID
    GROUP BY Assessment.ASMID)
UNION ALL
(SELECT DISTINCT
    Assessment.Amount,
    Assessment.ASMID,
    Assessment.Description,
    null,
    null,
    null,
    null
FROM Assessment
    LEFT JOIN Payment ON Payment.ASMID=Assessment.ASMID
    GROUP BY Assessment.ASMID)
ORDER BY ASMID, PMT_ASMID;

which gives me,

+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+
| AmountDue | ASMID | ASM_Descr          | PMT_Descr   | PaymentsApplied | PaymentList | PMT_ASMID |
+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+
|      NULL |  NULL | NULL               | Deposit Fee |        600.0000 | 4           |         0 |
|      NULL |  NULL | NULL               | Rent Fee    |       1100.0000 | 1,2         |         1 |
|      NULL |  NULL | NULL               | Damage Fee  |         20.0000 | 3           |         2 |
| 1200.0000 |     1 | Rent               | NULL        |            NULL | NULL        |      NULL |
|   20.0000 |     2 | Damage - car break | NULL        |            NULL | NULL        |      NULL |
|  500.0000 |     3 | Damage - vehicle   | NULL        |            NULL | NULL        |      NULL |
+-----------+-------+--------------------+-------------+-----------------+-------------+-----------+

But, this one is still missing one row from Payment row (RID: 5) and I'm not getting the expected result.

Answer 1

I'd first collect all assessments, left join them to the payments and then union all payments without assessments:

# Assessments with payments

SELECT asm.AID,
       asm.ASMID,
       min(asm.Amount) AS AmountDue,
       SUM(pam.Amount) AS PaymentsApplied,
       asm.Description AS `ASM-Descr`,
       pam.Description AS `PMT-Descr`,
       agr.FLAGS
FROM Assessment asm
LEFT JOIN Payment pam ON pam.ASMID = asm.ASMID
JOIN Agreement agr ON agr.AID = asm.AID
GROUP BY asm.AID,
         asm.ASMID
UNION # Payments without assessments

SELECT pam.AID,
       pam.ASMID,
       NULL AS AmountDue,
       SUM(pam.Amount) AS PaymentsApplied,
       NULL AS `ASM-Descr`,
       pam.Description AS `PMT-Descr`,
       agr.FLAGS
FROM Payment pam
LEFT JOIN Assessment asm ON pam.ASMID = asm.ASMID
JOIN Agreement agr ON agr.AID = pam.AID
WHERE asm.ASMID IS NULL
GROUP BY pam.AID, pam.RID;

If you want to add more information you could wrap this result, give it a name and join more tables to the temporary result:

SELECT payment_overview.*,
       p.name
FROM
    ( # Assessments with payments
 SELECT asm.AID,
        asm.ASMID,
        min(asm.Amount) AS AmountDue,
        SUM(pam.Amount) AS PaymentsApplied,
        asm.Description AS `ASM-Descr`,
        pam.Description AS `PMT-Descr`,
        agr.FLAGS
     FROM Assessment asm
     LEFT JOIN Payment pam ON pam.ASMID = asm.ASMID
     JOIN Agreement agr ON agr.AID = asm.AID
     GROUP BY asm.AID,
              asm.ASMID
     UNION # Payments without assessments
 SELECT pam.AID,
        pam.ASMID,
        NULL AS AmountDue,
        SUM(pam.Amount) AS PaymentsApplied,
        NULL AS `ASM-Descr`,
        pam.Description AS `PMT-Descr`,
        agr.FLAGS
     FROM Payment pam
     LEFT JOIN Assessment asm ON pam.ASMID = asm.ASMID
     JOIN Agreement agr ON agr.AID = pam.AID
     WHERE asm.ASMID IS NULL
     GROUP BY pam.AID,
              pam.RID ) AS payment_overview
JOIN Payor p ON p.AID = payment_overview.AID ;