CODEWITHSUNDEEP
oracle-databasesubquery
user6016731
user6016731

Reputation: 382

Oracle SQL subqueries to link multiple tables

I have 3 different datasets

Data1:

ID  Rank_down
1   
2   
3

data2:

ID  From    To
1   AB     CD
2   EF     JK
3   PQ  PQ

data3:

From/To  rank
  AB     1
  CD     2
  EF     3
  JK     5
  PQ     9

I need to populate the field rank_down as Y/N based on whether the ID has moved from a higher rank to a lower rank (1 being the lowest).This has to be done in Oracle SQl. I am new to this programming hence finding it difficult to write a subquery.

Can any one help?

Upvotes: 0

Views: 165

Answers (2)

krokodilko
krokodilko

Reputation: 36107

Try:

select t.*, 
      CASE WHEN 
           ( SELECT "RANK" FROM DATA_3 d
             WHERE d.From_To = t."FROM"
           )
           >
           ( SELECT "RANK" FROM DATA_3 d
             WHERE d.From_To = t."TO"
           )
        THEN 'Y' ELSE 'N' END
        As rank_down 
from data_2 t;

Upvotes: 1

xQbert
xQbert

Reputation: 35323

I'm just not following so I'll try some SQL and see if this gets us closer...

Does this select give the desired results in a select fashion? (I may have the Y/N's inverted)

SELECT ID, Case when D3a.Rank < D3b.Rank then 'N' 
                when D3a.Rank > D3b.Rank then 'Y'
                when D3a.Rank = D3b.Rank then 'N'
                end as Rank_down
FROM data2 D2
INNER JOIN data3 D3a
 on D3a.From_To = D2.From
INNER JOIN data3 D3b
 on D3b.From_To = D2.To

if so we just need to do an update based on it.

UPDATE Data1 d1
INNER JOIN (    SELECT ID, Case when D3A.Rank < D3b.Rank then 'N' 
                    when D3A.Rank > D3B.Rank then 'Y'
                    when D3A.Rank = D3B.Rank then 'N'
                    end as Rank_down
    FROM data2 D2
    INNER JOIN data3 D3a
     on D3a.From_To = D2.From
    INNER JOIN data3 D3b
     on D3B.From_To = D2.To) B
on B.ID = d1.ID 
set d1.Rank_down = B.Rank_Down

Upvotes: 0

Related Questions