CODEWITHSUNDEEP
oracle-databasestored-procedures
HEEN
HEEN

Reputation: 4727

Get STATUS from table by which join in oracle

I have a query for which based on CRNO I get the STATUS from another table. So the below query is

select a.crno, a.crno_date, a.state, a.status_rank from R4G_OSP.ENODEB a Where a.crno is not null and a.crno = 'R4G-MH-NLD-7718' and a.status_rank is not null order by 4 asc;

and STATUS table query is

select * from APP_WFM.WFM_CANDIDATE_STATUS where rank = 20

So, now I want to join the query and get the status in the first query. How should I do that

The sample data of both query is below

QUERY 1 SAMPLE DATA

IMG 1

QUERY 2 SAMPLE DATA

IMG 2

Please suggest how should I get the STATUS by joining it

Upvotes: 1

Views: 39

Answers (2)

Bishal Shrestha
Bishal Shrestha

Reputation: 1

 Select a.crno, 
     a.crno_date, 
     a.state, 
     a.status_rank 
   from R4G_OSP.ENODEB  a
   join APP_WFM.WFM_CANDIDATE_STATUS on APP_WFM.WFM_CANDIDATE_STATUS.RANK = a.status_rank
   and a.crno = 'R4G-MH-NLD-7718'
   and a.status_rank is not null
   order by 4 asc;

Based on the sample data of the table provided it is seen that the primary key relationship is set on WFM.WFM_CANDIDATE_STATUS.RANK and status_rank of another table so you can easily join between those columns

Upvotes: 0

Alberto Moro
Alberto Moro

Reputation: 1013

You can use JOIN. Manual Here

select a.crno, 
       a.crno_date, 
       a.state, 
       a.status_rank,
       APP_WFM.WFM_CANDIDATE_STATUS.STATUS
from R4G_OSP.ENODEB  a
join APP_WFM.WFM_CANDIDATE_STATUS on APP_WFM.WFM_CANDIDATE_STATUS.RANK = a.status_rank
Where a.crno is not null
and a.crno = 'R4G-MH-NLD-7718'
and a.status_rank is not null
order by 4 asc;

If you want only rank 20 add AND a.status_rank = 20 before ORDER BY

Upvotes: 1

Related Questions