CODEWITHSUNDEEP
databaseoracle-databaseoracle11ginner-join
Zeb-ur-Rehman
Zeb-ur-Rehman

Reputation: 1209

Conditional Inner Join in Oracle

I have an issue regarding conditional inner join. Kindly have a look at the following sqlfiddle.

http://sqlfiddle.com/#!4/6dc88

Here i'm trying to get all the same name. but if in case the id is 1 then also get the same name and the name which has id of 0. Your help will be appriciated. thanks. here is the example

Table1 
Id           |            Name
1            |            A
2            |            B
3            |            C

Table2
Id           |            Name
1            |            a
2            |            b
3            |            c
0            |            d

Here is what i expect 

A            |            a
B            |            b
C            |            c
A            |            d

Edit: Sorry i forget to mention my query, here is what i have tried so far..

select t1.name, t2.name from table1 t1
inner join table2 t2 on 
CASE
 WHEN t1.id = t2.id_copy and t1.id = 1 THEN 
    0
    else
    t1.id
END = t2.id_copy

Thanks in advance.

Upvotes: 2

Views: 5067

Answers (2)

Boneist
Boneist

Reputation: 23578

Assuming you mean that if table2.id is 0 then it should match with table1.id = 1, then this should do the trick:

with table1 as (select 1 id, 'A' name from dual union all
                select 2 id, 'B' name from dual union all
                select 3 id, 'C' name from dual),
     table2 as (select 1 id, 'a' name from dual union all
                select 2 id, 'b' name from dual union all
                select 3 id, 'c' name from dual union all
                select 0 id, 'd' name from dual)
select t1.name, t2.name
from   table1 t1
       inner join table2 t2 on (t1.id = case when t2.id = 0 then 1 else t2.id end);

NAME NAME_1
---- ------
A    a     
B    b     
C    c     
A    d

If there's more complicated logic around what decides how non-matching (t1.id = t2.id) rows in table2 match with table1, then you'll have to explain the logic.

Upvotes: 4

ytg
ytg

Reputation: 1857

Personally I would prefer to use something simpler, like:

select t1.name, t2.name from table1 t1
inner join table2 t2
on t1.id = t2.id_copy or (t1.id = 1 and t2.id_copy = 0)

Upvotes: 3

Related Questions