Untitled

MYSQL Select Query with UNION to mege data from three tables which shares the same FKey

SELECT firm, store_name, MONTH, sales_4, vat_4, input_vat_4

FROM (select `company_group`.`group_company_name`as firm,`store`.`store_name`as store_name, MONTHNAME( receipt_date ) AS MONTH,0 AS sales_4, 0 as vat_4,sum(`input_vat_4_percent`)as input_vat_4 from

company_group,store, Stock_recpt

WHERE `company_group`.`group_company_id` = `Stock_recpt`.`group_company_id`and `store`.`store_id` = `Stock_recpt`.`store_id` and  `Stock_recpt`.`purchase_type` = 'purchase_4_percent'

UNION

 

SELECT `company_group`.`group_company_name`as firm , `store`.`store_name`as store_name , MONTHNAME(date) AS MONTH , sum( `warehouse_sales`.`sales_four_percent`)as sales_4, sum( `warehouse_sales`.`vat_four_percent` ) AS vat_4, 0 as input_vat_4

 

from company_group,store, warehouse_sales

WHERE `company_group`.`group_company_id` = `warehouse_sales`.`firm_name`and `store`.`store_id` = `warehouse_sales`.`store_name`  

 

UNION

 

SELECT `company_group`.`group_company_name` as firm, `store`.`store_name`as store_name,MONTHNAME( sale_date ) AS MONTH, sum( `sales_report`.`sales_four_percent`)as sales_4, sum( `sales_report`.`vat_four_percent` ) AS vat_4,0 as input_vat_4

 

FROM company_group,store, sales_report

WHERE `company_group`.`group_company_id` = `sales_report`.`group_company_id`and `store`.`store_id` = `sales_report`.`store_id`)a

GROUP BY firm , store_name,`MONTH`