CODEWITHSUNDEEP
sqldatabase
Zain Deeb
Zain Deeb

Reputation: 39

join, union 3 tables in sql

Please, help me to solve my sql problem. In my DB I have 3 tables as following enter image description here

I have wrote a query that display the total refunded and total invoiced and the method has been used to refund or invoice (cash, credit card or checkout) for a specific order.

my query is as following:

SELECT Method, SUM(total_invoiced), SUM(total_refunded)
 FROM

(SELECT refunds.method_rf AS Method, 0 AS total_invoiced,
   SUM(refunds.ref_value) AS total_refunded
FROM refunds LEFT JOIN orders ON(refunds.order_id = orders.entity_id)
WHERE (orders.entity_id = 3)
GROUP BY Method
union
SELECT invoices.method_in as Method, SUM(invoices.inv_value) AS 
total_invoiced, 0 AS total_refunded
FROM invoices left JOIN orders ON(invoices.order_id = orders.entity_id)
WHERE (orders.entity_id = 3)
GROUP BY Method)main_table
GROUP BY Method

the output is what I expected. Here it is

output

My question is: Is there anyway to rewrite my query, in-order to delete my repeated condition which is WHERE (orders.entity_id = 3) and the query will look like this 

SELECT Method, SUM(total_invoiced), SUM(total_refunded)
 FROM

(SELECT refunds.method_rf AS Method, 0 AS total_invoiced,
   SUM(refunds.ref_value) AS total_refunded
FROM refunds LEFT JOIN orders ON(refunds.order_id = orders.entity_id)
GROUP BY Method
union
SELECT invoices.method_in as Method, SUM(invoices.inv_value) AS 
total_invoiced, 0 AS total_refunded
FROM invoices left JOIN orders ON(invoices.order_id = orders.entity_id)
GROUP BY Method)main_table
"PUT YOUR CONDITION"
GROUP BY Method

Upvotes: 1

Views: 2516

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You should be using union all. union incurs overhead of removing duplicates. The following is a little simpler -- you don't need the orders table:

SELECT Method, SUM(total_invoiced), SUM(total_refunded)
FROM ((SELECT r.method_rf AS Method, 0 AS total_invoiced, SUM(r.ref_value) AS total_refunded
       FROM refunds r
       WHERE r.order_id = 3
       GROUP BY i.Method
      ) UNION ALL
      (SELECT i.method_in as Method, SUM(i.inv_value) AS total_invoiced, 0 AS total_refunded
       FROM invoices i
       WHERE i.order_id = 3
       GROUP BY i.Method
      )
     ) ir
GROUP BY Method;

I would leave the filters in each subquery for performance reasons. If you only care about one order, then filtering before aggregating is a win performance-wise.

Upvotes: 1

Related Questions