CODEWITHSUNDEEP
sqloracle-databasereplacecase
SCS
SCS

Reputation: 1212

Conditional Join with replace and case

I have two tables that I want to join.

The case is in table 1 the default value is 1 then in table 2 the default value is 0

Table 1       Table 2
--------     ---------
1            0
1232342      1232342
1241232      1231231

I want to join table 1 and table 2 with condition that if table 2 is 0 then it will be replaced to 1.

SELECT T1.ID 
FROM TABLE1 T1, TABLE2 T2
WHERE T1.ID = REPLACE(CASE WHEN T2  = 0 THEN 1 ELSE T2.ID END, 'X', 'E')

with this statement it does not return the other id's that are not 0 or 1

expected output

Table 1      
--------     
1           
1232342

Upvotes: 0

Views: 1367

Answers (4)

jarlh
jarlh

Reputation: 44795

Switch to modern, explicit JOIN syntax. Skip the case expression, simply use AND/OR instead:

SELECT T1.ID 
FROM TABLE1 T1
JOIN TABLE2 T2
    ON T1.ID = T2.ID OR (T1.ID = 1 and T2.ID = 0)

Or use INTERSECT:

SELECT ID FROM TABLE1
INTERSECT
SELECT case when ID = 0 then 1 else id end from TABLE2

Upvotes: 0

user330315
user330315

Reputation:

Use a join with a CASE. replace() is for string values:

select t1.*
from table1 t1
  join table2 t2 on t1.id = case when t2.id = 0 then 1 else t2.id end;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37483

Try using case when :

SELECT * 
    FROM TABLE1 T1 inner join TABLE2 T2
    on T1.ID = (CASE WHEN T2.ID =0 THEN 1 ELSE T2.ID END)

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

use case when and sub-query

select t1.* from table1 t1 join    
(
select case when t2.id=0 then 1 else t2.id end as id  from table2 t2
) as t3 on t1.id=t3.id

Upvotes: 0

Related Questions