Oracle SQL Loop through rows

Question

I have 3 tables:

table1:

table2:

id  ---- flag  
111 ---- yes  
222 ---- yes  
444 ---- yes

table3:

id  ---- flag  
111 ---- no  
222 ---- yes  
333 ---- yes

I want to create a new table that populates with 'yes' to '1' and 'no' or non-existent to '0', but only those records which have at least one 'no' or non-existent as such:

new table:

id  ------- table2flag ----------- table3flag
111 -------  1 ------------------- 0    
333 -------- 0 ------------------- 1  
444 -------- 1 ------------------- 0

Notice how there is no entry for 222 since they are both yes in both tables. How can I achieve this with Oracle sql?

jarlh · Accepted Answer · 2016-04-07 12:24:05Z

1

Do LEFT JOIN's. Use coalesce to return 'no' instead of null.

select t1.id, coalesce(t2.flag, 'no') table2flag, coalesce(t3.flag, 'no') table3flag
from table1 t1
  left join table2 t2 on t1.id = t2.id
  left join table3 t3 on t1.id = t3.id

Edit: To skip rows with same id existing in both table2 and table3, both with flag = true, simply add that condition in a WHERE clause. Use case to return 1 for yes, otherwise 0.

select t1.id,
       case when t2.flag = 'yes' then 1 else 0 end as table2flag,
       case when t3.flag = 'yes' then 1 else 0 end as table3flag
from table1 t1
  left join table2 t2 on t1.id = t2.id
  left join table3 t3 on t1.id = t3.id
where not (t2.flag = 'yes' and t3.flag = 'yes')

edited Apr 7, 2016 at 12:24

answered Apr 7, 2016 at 10:55

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

waffle Over a year ago

that was really helpful but i forgot to add some details to my request. can you assist? thanks :)

waffle Over a year ago

almost there, but i only want to skip if the id is present on both tables as 'yes'.

kedar kamthe · Accepted Answer · 2016-04-07 12:31:11Z

0

try this

select table1.Id, DECODE (table2.flag, 'yes',1,0) flag1 ,DECODE (table3.flag, 'yes',1,0) flag2 from  table1 left join table2
 on table1.id = table2.id
 left join table3  on table1.id = table3.id 
 WHERE NOT(DECODE (table2.flag, 'yes',1,0)=1 and DECODE (table3.flag, 'yes',1,0)=1)

edited Apr 7, 2016 at 12:31

answered Apr 7, 2016 at 12:19

kedar kamthe

8,20811 gold badges38 silver badges46 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Loop through rows

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related