CODEWITHSUNDEEP
sqloracle-database
Erwin
Erwin

Reputation: 61

Inner join table a or b based on existence

I've got these 3 tables:

Table A

---------------------------
|KEY | VALUE_A1 | VALUE_A2|
---------------------------

Table B

------------------------------
|KEY | APPLICATION | VALUE_B1|
------------------------------

Table C

------------------------------
|KEY | APPLICATION | VALUE_C1|
------------------------------

I want to return A.VALUE_A1, A.VALUE_A2 along with B.VALUE_B1 or C.VALUE_C1 based on the existence of the application in B or C. This application is given in de where-clause in de script.

Example: Table A has 10 records. Table B contains 6 records with application 'APP1'. Table C contains 4 records with application 'APP2'. Both table B and C are through KEY connected to table A. If the application given is 'APP1' then I want to return B.VALUE_B1. If 'APP2' is given I want to return C.VALUE_C1.

Is it possible to INNER JOIN on one of two tables based on a CASE-like condition?

Upvotes: 1

Views: 528

Answers (1)

StanislavL
StanislavL

Reputation: 57421

Not INNER JOIN but LEFT JOIN like this

select A.KEY
       A.VALUE_A1, A.VALUE_A2,
       CASE WHEN B.VALUE_B1 IS NOT NULL THEN B.VALUE_B1 ELSE C.VALUE_C1 END AS RESULT_VALUE
from A
    LEFT JOIN B on A.key=B.key
    LEFT JOIN C on A.key=C.key

UPDATE: There is an alternative

select A.KEY
       A.VALUE_A1, A.VALUE_A2,
       sub.VALUE AS RESULT_VALUE
from A
    INNER JOIN (SELECT KEY, VALUE_B1 as VALUE
                FROM B
                UNION ALL
                SELECT KEY, VALUE_C1 as VALUE
                FROM C) sub on A.KEY=sub.KEY

Upvotes: 3

Related Questions