UNION query issue in MySQL-CodePudding

Look at my UNION query:

SELECT customer_id
     , name as cname
     , CASE
        WHEN address <> ''
          THEN CONCAT(address, ', ', city)
        ELSE city
       END as address
     , null as total_sale
     , null as total_paid
     , null as total_due
FROM customer c

UNION 

SELECT customer_id
     , null as cname
     , null as address
     , sum(amount_due_tot) as total_sale
     , sum(amount_paid_tot) as total_paid
     , sum(amount_due_balance_tot) as total_due
FROM (
  SELECT bad.basket_id as baskets
       , bad.customer_id as customer_id
       , bad.amount_due as amount_due_tot
       , sum(COALESCE(p.amount_paid, 0)) as amount_paid_tot
       , bad.amount_due - SUM(COALESCE(p.amount_paid, 0)) as amount_due_balance_tot
       , sale_status_id
  FROM basket_amount_due bad
    LEFT JOIN basket b USING (basket_id)
    LEFT JOIN basket_payment p USING (basket_id)
    LEFT JOIN customer c ON b.customer_id = c.customer_id
  WHERE b.sale_status_id = 4
  GROUP BY basket_id, sale_status_id
) d
GROUP BY customer_id;

It returns a result as shown here:

 ------------- -------------------- ----------------- ------------ ------------ ----------- 
| customer_id | cname              | address         | total_sale | total_paid | total_due |
 ------------- -------------------- ----------------- ------------ ------------ ----------- 
|           1 | Customer Name 01   | NULL            |       NULL |       NULL |      NULL |
|           2 | Customer Name 02   | Address 02      |       NULL |       NULL |      NULL |
|           3 | Customer Name 03   | Address 03      |       NULL |       NULL |      NULL |
|           4 | Customer Name 04   | Address 04      |       NULL |       NULL |      NULL |
|           5 | Customer Name 05   | Address 05      |       NULL |       NULL |      NULL |
|           6 | Customer Name 06   |                 |       NULL |       NULL |      NULL |
|           7 | Customer Name 07   |                 |       NULL |       NULL |      NULL |
|           1 | NULL               | NULL            |     927.08 |     927.08 |      0.00 |
|           3 | NULL               | NULL            |    2576.80 |    2376.80 |    200.00 |
|           4 | NULL               | NULL            |   12118.00 |    4210.25 |   7907.75 |
|           6 | NULL               | NULL            |     870.00 |     270.00 |    600.00 |
 ------------- -------------------- ----------------- ------------ ------------ -----------

But from this UNION query I expect to get this result:

 ------------- -------------------- ----------------- ------------ ------------ ----------- 
| customer_id | cname              | address         | total_sale | total_paid | total_due |
 ------------- -------------------- ----------------- ------------ ------------ ----------- 
|           1 | Customer Name 01   | NULL            |     927.08 |     927.08 |      0.00 |
|           2 | Customer Name 02   | Address 02      |       NULL |       NULL |      NULL |
|           3 | Customer Name 03   | Address 03      |    2576.80 |    2376.80 |    200.00 |
|           4 | Customer Name 04   | Address 04      |   12118.00 |    4210.25 |   7907.75 |
|           5 | Customer Name 05   | Address 05      |     870.00 |     270.00 |    600.00 |
|           6 | Customer Name 06   |                 |       NULL |       NULL |      NULL |
|           7 | Customer Name 07   |                 |       NULL |       NULL |      NULL |
 ------------- -------------------- ----------------- ------------ ------------ -----------

Can anybody tell me what is wrong I have made in this query?

CodePudding user response：

Instead of union you need to use join.

SELECT c.customer_id
     , c.name as cname
     , CASE
        WHEN address <> ''
          THEN CONCAT(c,address, ', ', c.city)
        ELSE city
       END as address
     , sum(d.amount_due_tot) as total_sale
     , sum(d.amount_paid_tot) as total_paid
     , sum(d.amount_due_balance_tot) as total_due
FROM customer c
Left join
(
  SELECT bad.basket_id as baskets
       , bad.customer_id as customer_id
       , bad.amount_due as amount_due_tot
       , sum(COALESCE(p.amount_paid, 0)) as amount_paid_tot
       , bad.amount_due - SUM(COALESCE(p.amount_paid, 0)) as amount_due_balance_tot
       , sale_status_id
  FROM basket_amount_due bad
    LEFT JOIN basket b USING (basket_id)
    LEFT JOIN basket_payment p USING (basket_id)
    LEFT JOIN customer c ON b.customer_id = c.customer_id
  WHERE b.sale_status_id = 4
  GROUP BY basket_id, sale_status_id
) d
on c.customer_id = d.customer_id
GROUP BY c.customer_id;

CodePudding user response：

The second part of your query selects all baskets with a due amount along with the customers. You want one result row per customer. So why do you group by basket and sale status? And why do you null the address? This doesn't seem to make much sense. I'd expect a mere:

SELECT c.customer_id
     , c.name AS cname
     , CONCAT_WS(', ', NULLIF(c.address, ''), c.city) AS address
     , SUM(p.amount_due) AS total_sale
     , COALESCE(SUM(p.amount_paid), 0) AS total_paid
     , SUM(p.amount_due) - COALESCE(SUM(p.amount_paid), 0) AS total_due
FROM basket_amount_due bad
JOIN basket b USING (basket_id)
LEFT JOIN basket_payment p USING (basket_id)
JOIN customer c ON c.customer_id = bad.customer_id
WHERE b.sale_status_id = 4
GROUP BY c.customer_id
ORDER BY c.customer_id;

This only shows customers that have an entry in the basket_amount_due table. If you want all customers, then select from customer and outer join the other tables.

This query also assumes that there is at most one row in basket_amount_due per basket. So does yours. If this isn't the case, then don't join its rows with the basket_payment rows, but aggregate before joining. Else you'd get incorrect results.