mysql - SQL Query with union and join

Question

my set-up is like this

Table1: company_group - company_id, company_name
Table2: store         - store_id, store_name
Table3: sales         - sales_id, company_id, store_id, date, sales
Table4: wh_sales      - wh_sale_id, company_id, store_id, date, sales

Table5: purchase      - purchase_id, company_id, store_id, date, purchase

Now I am able to get the data for the first four tables using the select and union query, but I can't make out how and which join I should use to get the data for the table5 in the same table

I am using the query for the first four tables like

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales` 
from company_group, store,sales
where `company_group`.`company.id`=`sales`.`company.id`
and `store`.`store.id`=`sales`.`store.id`
group by company_name,store_name, 'MONTH'

UNION

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`
from company_group, store,wh_sales
where `company_group`.`company.id`=`wh_sales`.`company.id`
and `store`.`store.id`=`wh_sales`.`store.id`
group by company_name,store_name, 'MONTH'

now how can I include the Table5 so that I can get the result like

company_name store_name   month      sales    purchase
company-a     store-c    December    40000    45000

Answer 1

Use the following query

select `company_name, `store_name`, MONTH, sum(`sales`) as sales, sum(purchase) as purchase   from (
select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`sales`.`sales` , 0 as purchase
from company_group, store,sales
where `company_group`.`company.id`=`sales`.`company.id`
and `store`.`store.id`=`sales`.`store.id`

UNION

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH,`wh_sales`.`sales`, 0 as purchase
from company_group, store,wh_sales
where `company_group`.`company.id`=`wh_sales`.`company.id`
and `store`.`store.id`=`wh_sales`.`store.id`

UNION

select `company_group`.`company_name, `store`.`store_name`, MONTHNAME(date) AS MONTH, 0 as sales, purchase
from company_group, store,purchase
where `company_group`.`company.id`=`purchase`.`company.id`
and `store`.`store.id`=`purchase`.`store.id`) a
group by company_name,store_name, 'MONTH'

The group by clause is not needed for the inner queries since there is no group function. I have moved it to the outermost query