How to add two complex SQL Queries and get output in single query?

Question

I am new to coding and was preparing a SQL command but stuck at a step. Below is the main SQL Query :

select 
distinct 
hap.position_code as RequisitionNumber, 
COALESCE(hap.attribute10,'UNIVERSAL') as RequisitionType, hap.name as 
RequisitionTitle, 
paam1.job_id as RequisitionTemplate, COALESCE(hap.attribute11,'1') as 
NumberToHire, 

pu.username as HiringManagerLogin, 

paam1.job_id as JobField, 
hap.full_part_time as Schedules, hap.position_code as PositionCode  
from hr_all_positions hap, per_all_assignments_m paam1, per_users pu 
where hap.position_id = paam1.position_id
and hap.position_id in
(
select distinct paam.position_id from per_all_assignments_m paam, 
per_periods_of_service ppos 
where paam.person_id = ppos.person_id and paam.position_id is not null and 
ppos.actual_termination_date >= sysdate
)

in the above query I am taking the value of pu.username from another query which is attached below:

select pu.username from per_users pu where person_id = (
select pmhd.manager_id from PER_MANAGER_HRCHY_DN pmhd, 
per_periods_of_service ppos 
where pmhd.person_id = ppos.person_id and pmhd.IMMEDIATE_REPORTEE_ASG_ID is 
not null 
and pmhd.EFFECTIVE_END_DATE = ppos.actual_termination_date and 
ppos.actual_termination_date >= sysdate 
and pmhd.person_id = (select distinct paam.person_id from 
per_all_assignments_m paam, per_periods_of_service ppos 
where paam.person_id = ppos.person_id and paam.position_id is not null and 
ppos.actual_termination_date >= sysdate) 
)

Now I when I join both the query like below:

select 
distinct 
hap.position_code as RequisitionNumber, 
COALESCE(hap.attribute10,'UNIVERSAL') as 
RequisitionType, hap.name as RequisitionTitle, 
paam1.job_id as RequisitionTemplate, COALESCE(hap.attribute11,'1') as 
NumberToHire, 

pu.username as HiringManagerLogin, 

paam1.job_id as JobField, 
hap.full_part_time as Schedules, hap.position_code as PositionCode  
from hr_all_positions hap, per_all_assignments_m paam1, per_users pu 
where hap.position_id = paam1.position_id and paam1.person_id = pu.person_id
and pu.person_id = (
select pmhd.manager_id from PER_MANAGER_HRCHY_DN pmhd, per_periods_of_service 
ppos 
where pmhd.person_id = ppos.person_id and pmhd.IMMEDIATE_REPORTEE_ASG_ID is 
not null 
and pmhd.EFFECTIVE_END_DATE = ppos.actual_termination_date and 
ppos.actual_termination_date >= sysdate 
and pmhd.person_id = (select distinct paam.person_id from 
per_all_assignments_m 
paam, per_periods_of_service ppos 
where paam.person_id = ppos.person_id and paam.position_id is not null and 
ppos.actual_termination_date >= sysdate) 
)
and hap.position_id in
(
select distinct paam.position_id from per_all_assignments_m paam, 
per_periods_of_service ppos 
where paam.person_id = ppos.person_id and paam.position_id is not null and 
ppos.actual_termination_date >= sysdate
)

I am Trying this is Oracle database No Data output is coming, I don't understand why?

PLEASE HELP ME !!

Thanks in advance, Shivam

Answer 1

To be honest, your final query output looks like it was a proper combination of the other two. However, you might not be getting any output because of the additional join condition:

paam1.person_id = pu.person_id

This is not in either of your original queries, and every row probably fails this join condition. Try removing it to see if any rows are output. Try adding it to your first query. If rows are output, then it probably means that your additional filter on pu.person_id is very selective.