CODEWITHSUNDEEP
sqloracle-database
thatdude99
thatdude99

Reputation: 27

Conditional Join in Oracle SQL only returning values where not null

I have two tables I need to join.

Table A
-----
level, x, y, z


Table B
-------------
a, b, c, d

I need to join on y = c, z = d and then a condition.

Level can have values of 1-5.

Pseudo code follows...

SELECT *
FROM a
join b
on a.y = b.c, a.z = b.d,
case
  when a.level = 1
    then b.a
  when a.level = 2
    then b.b
  else null
end = a.x

the above won't work due to the null = a.x issue. If I change it to say 

case
  when a.level = 1
    then b.a
  when a.level = 2
    then b.b
  else a.x
end = a.x

It only returns values where a.x is not null.

How can I solve this?

Examples:

Table A
-----
1, matt, sam, bob
2, mark, sean, john
5, 1, 2, 3


Table B
-------------
matt, joe, sam, bob
steve, mark, sean, john
4, 5, 2, 3

Results
-----
1, matt, sam, bob, matt, joe, sam, bob
2, mark, sean, john, steve, mark, sean, john
5, 1, 2, 3, 4, 5, 2, 3

Upvotes: 0

Views: 384

Answers (2)

forpas
forpas

Reputation: 164089

Add a condition a.level is null combined with or to your CASE statement:

SELECT * FROM a JOIN b
on a.y = b.c and a.z = b.d and (
  a.level is null 
  or
  a.x = case a.level
    when 1 then b.a
    when 2 then b.b
  end 
)

See the demo.
Results:

> level | X    | Y    | Z    | A     | B    | C    | D   
> ----: | :--- | :--- | :--- | :---- | :--- | :--- | :---
>     1 | matt | sam  | bob  | matt  | joe  | sam  | bob 
>     2 | mark | sean | john | steve | mark | sean | john

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Don't use case. Just use boolean comparisons:

select *
from a join b
     on a.y = b.c and
        a.z = b.d and
        ( (a.level = 1 and a.x = b.a) or
          (a.level = 2 and a.x = b.b) or
          (a.level not in (1, 2))
        )

If you do use case, you need to realize that NULL is going to cause the comparison to fail. You would need to use a.x for the comparison to work (in general but not when a.x is NULL).

Upvotes: 2

Related Questions