CODEWITHSUNDEEP
sqloracle-databasesyntaxjoincase
AndrewMurphy
AndrewMurphy

Reputation:

Can I join to a table in ORACLE (10g) using a CASE clause in the ON statement (or even where clause as it's an inner join)

I'm trying to make the following code smaller. Is this possible?

select a.*
from table1 a
WHERE a."cola1" = 'valuea1'
UNION ALL
select a.*
from tablea1 a
inner join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
  and b."colb3" = 'valueb3'

In effect I'm looking for records from table1 for value1 or value2, but for records matching value2 I want to apply 1 extra condition which involves a join to a 2nd table Can this be done without a UNION clause?

A skeleton or what I'm trying to code is below....but it's not working naturally.

select a.*
from table1 a
inner join table2 b on a."cola1" = b."COLb1"
WHERE a."cola2" IN ('valuea1','valuea2')
  and 
  CASE 
    WHEN a."cola2" = 'valuea2' THEN b."colb1" = 'valueb3'
    ELSE 1=1
  END CASE

Upvotes: 1

Views: 21356

Answers (4)

Joshan George
Joshan George

Reputation: 668

You can achieve this by using left join and where condition

select a.*
from table1 a
left join tablea2 b on a."cola2" = b."colb2"
WHERE a."cola1" = 'valuea2'
  and ( b."colb2" is null or b."colb3" = 'valueb3' )

Upvotes: 0

Hosam Aly
Hosam Aly

Reputation: 42463

I think CASE statements work in join conditions, but I'm not sure. But would this work for you?

select *
  from table1 a
 where a.cola1 = 'valuea1'
    or (a.cola1 = 'valuea2'
        and Exists(select 1
                     from table2 b
                    where a.cola2 = b.colb2
                      and b.colb3 = 'valueb3'
                   )
        )

Edit: Wouldn't this simply work?

select a.*
  from table1 a
  Left Outer Join table2 b On (a.cola2 = b.colb2)
 where a.cola1 = 'valuea1'
    or (a.cola1 = 'valuea2' and b.colb3 = 'valueb3')

Upvotes: 5

Dave Costa
Dave Costa

Reputation: 48121

Overall you should follow Hosam's suggestion of rewriting the predicate entirely. But to explain your original problem further, the issue is that in SQL, CASE .. END is an expression and can only be used where any other expression could be used. A condition like "a=b" is two expressions connected by a logical operator. You may want to think of it as a boolean expression but that's not the way SQL views it.

You could accomplish what you want with CASE by using it as one of the two expressions in the condition, like so:

WHERE a."cola2" IN ('valuea1','valuea2')
  and 
  b."colb1" = CASE 
                WHEN a."cola2" = 'valuea2' THEN 'valueb3'
                ELSE b."colb1"
              END CASE

(If it is possible for colb1 to include NULLs you would need to modify to handle that.)

Upvotes: 4

AndrewMurphy
AndrewMurphy

Reputation:

OP: I've got a mini-workaround which goes close (This may only work given this is an inner join.)

select a.* from table1 a inner join table2 b on a."cola1" = b."COLb1" WHERE (a."cola2" = 'valuea1') OR (a."cola2" = 'valuea2' and b."colb1" = 'valueb3')

Sometimes writing code out can prompt some alternative thinking. Self-Therapy sort of. Thanks for your input.

Upvotes: -1

Related Questions