KAVIYA

Dear Mr.Barand, Thanks for your response for my post. I am writing the query to get the output of actual and target of three category from different tables. When I am writing query separately each category I am getting output. But when i am joining those queries, I am getting errors. For example, the below query to get the target and actual of machine details. SELECT Month,Name, ProductionTotal, ProductionActual FROM ( select count(a.id) as ProductionActual, Month, Fname from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID ) ProductionTotal USING (Month) I am getting the below output. Next when i am writing the below query to get the output if customer target and actual. SELECT Month,Name,CustomerTotal,CustomerActual FROM ( select count(a.id) as CustomerActual, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-04' group by Month, EmpID ) CustomerTotal USING (Month, Name) I am getting the below output. When i am joining the above two query i am getting the category output only production output is not coming. Which ever is first, that output only coming. the join query is. SELECT Month,Name,CustomerTotal,CustomerActual, ProductionTotal, ProductionActual FROM ( select count(a.id) as CustomerActual, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-04' group by Month, EmpID ) CustomerTotal USING (Month, Name) LEFT JOIN ( select count(a.id) as ProductionActual, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProdAct USING (Month, Name) LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID ) ProductionTotal USING (Month, Name) and the join query output is single table is using to get the target and actual using above query But i am using two tables to get the target and actual for the third category marketing. Marketing query is SELECT Month,Fname,MarketingActual,MarketingTotal FROM ( select count(a.id) as MarketingActual, Month, Fname from sbms.marketing_data as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where b.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID) MarketingActual LEFT JOIN (select count(a.id) as MarketingTotal, Month, Fname as Name from sbms.marketing_target as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where b.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID) MarketingTotal USING (Month) and the output is But the marketing output should come like below image I need to combine all these three categories and the final output should come like below. Al my dumps are attached with the blow link for your reference. https://drive.google.com/file/d/1pRranTejVygkSaufLxjIbiiFdfyxlSDq/view?usp=sharing Please help me to solve this.

I'm getting results from the customer and production tables, but when I combine these two queries, I don't get any results from the tables.

1.Production Target and Production Actual was attached the below SQL query. SELECT Month,Name, ProductionTotal, ProductionActual FROM ( select count(a.id) as ProductionActual, Month, Fname from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID ) ProductionTotal USING (Month) The output was executed for Production Target and Actual 2.Customer Target and Customer Actual was attached the below the SQL query. SELECT Month,Name,CustomerTotal,CustomerActual FROM ( select count(a.id) as CustomerActual, Month, Fname from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-04' group by Month, EmpID ) CustomerTotal USING (Month) The output was executed for Customer Target and Actual 3.Marketing Target and Marketing Actual was attached the below the SQL query. It was not executed the query exact result. SELECT Month,Fname,MarketingActual FROM ( select count(a.id) as MarketingActual, Month, Fname from sbms.marketing_data as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where b.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID) MarketingActual LEFT JOIN (select count(a.id) as MarketingTotal, Month, Fname as Name from sbms.marketing_target as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where b.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID) MarketingTotal USING (Month) 4.If i joining two table Prodution and customer table output will be executed only for production query not in customer query. SELECT Month, Name, ProductionTotal, ProductionActual, CustomerTotal, CustomerActual FROM ( select count(a.id) as ProductionActual, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-01' group by Month, EmpID ) ProductionTotal USING (Month, Name) LEFT JOIN ( select count(a.id) as CustomerActual, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual USING (Month, Name) LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a inner join sbms.usertable as b on b.EmpNo = a.EmpID where a.Branch = '5' AND (b.Role='4' OR b.Role='5') AND Month='2024-04' group by Month, EmpID ) CustomerTotal USING (Month, Name) 5. Final output i want production ,customer,Marketing table within single query.