How to join multiple tables with multiple conditions

Question

I have six tables

online_transaction

| date | id | supplier_id | product code |

online_transaction_enc

| date | id | item |

offline_transaction

| date | id |

offline_transaction_enc

| date | id | item |

products

| type | product_code |

supplier

| supplier_id | country |

Select count(item) where date is between '2018-Jun-01' And '2018-July-30' AND Type='household' AND country='Malaysia'

These is roughly what I want to achieve. I want to union item from online and offline on date and id so I will get all items combine and then followed by the other requirements. How can do this in MySQL ?

Answer 1

Try this:

 select count(a.item) from 
    (select date, id, item from online_transaction_enc
    union 
    select date, id, item from offline_transaction_enc)a
    inner join 
    (
    select  date,id,supplier_id,productcode from online_transaction
    union 
    select  date,id,supplier_id,productcode from offline_transaction)b
    on a.date=b.date and a.id=b.id
    inner join supplier on b.supplier_id=supplier.supplier_id
    inner join products on b.productcode=products.product_code
    where a.date between '20180601' And '20180730' AND Type='household' AND country='Malaysia'