CODEWITHSUNDEEP
sqloracle-databasejoin
fonr
fonr

Reputation: 47

select a value from different table based on conditions in sql

I'm trying to select a value from a different table based on current table values and condition

Table 1:

C1 C2 C3
1 2 3
1 4 5
1 6 6
2 3 3

Table 2:

D1 D2 D3 D4
1 2 3 Value1
1 4 5 Value2
1 6 8 Value3
2 3 4 Value4
2 Value5

And Im trying to get the below expected result table a single line sql Results:

C1 C2 C3 D4
1 2 3 Value1
1 4 5 Value2
1 6 6
2 3 3 Value5

The condition is to pick D4 value only (C1=D1 and C2=D2 and C3=D3) matches then D4 or when C1=D1 matches then D4 else null for all

I tried inner join and also case statement but no success

Here is the fiddle i had created

Upvotes: 0

Views: 65

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

This looks like a left join:

select t1.*, t2.d4
from table1 t1 left join
     table2 t2
     on t1.C1 = t2.D1 and
        (t1.C2 = t2.D2 or t2.D2 is null) and
        (t1.C3 = t2.D3 or t2.D3 is null);

Note: It is a little hard to tell from the explanation if you need for both D2 and D3 to be NULL. If so:

select t1.*, t2.d4
from table1 t1 left join
     table2 t2
     on t1.C1 = t2.D1 and
        (t1.C2 = t2.D2 and t1.C3 = t2.D3 or
         t2.D2 is null and t2.D3 is null
        );

Here is the fiddle.

Upvotes: 1

Related Questions