CODEWITHSUNDEEP
oracle-database
user3814005
user3814005

Reputation: 7

Join the result with new inner query sql

select A.UNIT, A.LEASE_ID, A.MONTHS_GUARANTEED, A.MONTHLY_PAYMENT_AM, 
B.DATE_PAID, C.CHARGE_JOB from leasei A 
left outer join eq_capture B on A.LEASE_ID = B.LEASE_ID 
left outer join eq_mast C on A.UNIT = C.UNIT
where A.DATE_LEASE_EXPIRE = 0
ORDER BY A.LEASE_ID;

I want to use the result of the above query that is use the value C.CHARGE_JOB and the check with another table (job_infojc D) and get the D.STATE value with a where condition C.CHARGE_JOB = D.JOB

Any help is highly appreciated.

Upvotes: 0

Views: 36

Answers (1)

bugs2919
bugs2919

Reputation: 358

Like this: 

select D.STATE, X.*
from job_infojc D
join (select A.UNIT, A.LEASE_ID, A.MONTHS_GUARANTEED, A.MONTHLY_PAYMENT_AM, 
B.DATE_PAID, C.CHARGE_JOB from leasei A 
left outer join eq_capture B on A.LEASE_ID = B.LEASE_ID 
left outer join eq_mast C on A.UNIT = C.UNIT
where A.DATE_LEASE_EXPIRE = 0
) X on X.CHARGE_JOB = D.JOB
ORDER BY X.LEASE_ID;

Upvotes: 2

Related Questions