How to combine two queries into one output-CodePudding

I have two queries that help with roster cleanup in my system. The first query shows all headcount that is hourly or salaried and is in a wrong cost center. The second query shows all headcount that is linked with a closed station. Both hc I want to cleanup and show in a query.

SELECT emplid,
employee_login,
employee_full_name,
reports_to_level_7_employee_name,
job_level_name,                                                                                                                                                                                         
employee_business_title ,                                                                                                                                                                                       
reports_to_level_8_employee_name,
costcenter_id,
location_building_code,                                                                                                                                                                             
company_country_code,
employee_type_short_name,
CASE 
When department_id = '1092' and employee_type_short_name = 'S' then '1172'
When department_id = '1172' and employee_type_short_name = 'H' then '1092'
else employee_type_short_name
end as "incorrect_mapp"
FROM pm_employee_headcount_conf pehc
left join wbr_global.raw_station_extended_attribute rsea on pehc.location_building_code =      rsea.location_code
 WHERE current_transaction_flag = 'Y'
 and incorrect_mapp <> 'S'
 and incorrect_mapp <> 'H'
 and department_id in('1092','1172')    
and company_country_code in('USA','CAN')    
and job_level_name <>  99                                                                                                                                                                                           
 and job_level_name <> 0        
 and job_level_name <> 1    
 and job_level_name <> 2    



SELECT emplid,
employee_login,
employee_full_name,
reports_to_level_7_employee_name,
  job_level_name,                                                                                                                                                                                           
employee_business_title ,                                                                                                                                                                                       
reports_to_level_8_employee_name,
costcenter_id,
  location_building_code,                                                                                                                                                                               
company_country_code,
employee_type_short_name,
rsea.status 
FROM pm_employee_headcount_conf pehc
left join wbr_global.raw_station_extended_attribute rsea on pehc.location_building_code = rsea.ds
WHERE current_transaction_flag = 'Y'
and department_id in('1092','1172') 
and company_country_code in('USA','CAN')
and job_level_name <> 99                                                                                                                                                                                            
and job_level_name <> 0     
and job_level_name <> 1 
and job_level_name <> 2 
and status <> 'Active'
and status <> 'Launch'

CodePudding user response：

Use UNION ALL between the two queries; that will make it one query and append the results from the second to those from the first (the number and type of respective columns must be identical; naturally!)

CodePudding user response：

Use UNION operator to combine the result-set of two or more SELECT statements.

The columns must also have similar data types, in every SELECT statement must also be in the same order.

SELECT emplid,
employee_login,
employee_full_name,
reports_to_level_7_employee_name,
job_level_name,                                                                                                                                                                                         
employee_business_title ,                                                                                                                                                                                       
reports_to_level_8_employee_name,
costcenter_id,
location_building_code,                                                                                                                                                                             
company_country_code,
employee_type_short_name,
CASE 
When department_id = '1092' and employee_type_short_name = 'S' then '1172'
When department_id = '1172' and employee_type_short_name = 'H' then '1092'
else employee_type_short_name
end as "incorrect_mapp"
FROM pm_employee_headcount_conf pehc
left join wbr_global.raw_station_extended_attribute rsea on pehc.location_building_code =      rsea.location_code
 WHERE current_transaction_flag = 'Y'
 and incorrect_mapp <> 'S'
 and incorrect_mapp <> 'H'
 and department_id in('1092','1172')    
and company_country_code in('USA','CAN')    
and job_level_name <>  99                                                                                                                                                                                           
 and job_level_name <> 0        
 and job_level_name <> 1    
 and job_level_name <> 2    

UNION ALL 

SELECT emplid,
employee_login,
employee_full_name,
reports_to_level_7_employee_name,
  job_level_name,                                                                                                                                                                                           
employee_business_title ,                                                                                                                                                                                       
reports_to_level_8_employee_name,
costcenter_id,
  location_building_code,                                                                                                                                                                               
company_country_code,
employee_type_short_name,
rsea.status 
FROM pm_employee_headcount_conf pehc
left join wbr_global.raw_station_extended_attribute rsea on pehc.location_building_code = rsea.ds
WHERE current_transaction_flag = 'Y'
and department_id in('1092','1172') 
and company_country_code in('USA','CAN')
and job_level_name <> 99                                                                                                                                                                                            
and job_level_name <> 0     
and job_level_name <> 1 
and job_level_name <> 2 
and status <> 'Active'
and status <> 'Launch'