CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle12c
Abhishek
Abhishek

Reputation: 1068

Choose table to join in oracle depending on data

Let's say I have 3 table transaction,cust_type_1,cust_type_2.

   Transaction  
Cust_type   char - type of customer which can be 1 or 2
Cust_id number - id from their respective table i.e. cust_type_1 and cust_type2
Amount  number - amount

   Cust_type_1  
ct1_id  number - id(pk)
ct1_name    varchar - customer name


   Cust_type_2  
ct2_id  number - id(pk)
ct2_name varchar - customer name

Now i wish to see create a view where i can see name and amount only.

select COND_JOINED_TABLE_ALIAS.name,t.amount
from   transaction t
join   case when t.type = 1 JOIN CUST_TYPE_1
       else JOIN CUST_TYPE_2 END AS COND_JOINED_TABLE_ALIAS

How can i achieve conditional join based on type of data. Yes i know there can be better structure to avoid this use case. But this is just for example. Any help would be appreciated.

Upvotes: 0

Views: 44

Answers (3)

Pawan Rawat
Pawan Rawat

Reputation: 525

below sql can be used

SELECT
    CASE t.cust_type
        WHEN 1   THEN c1.ct1_name
        WHEN 2   THEN c2.ct2_name
    END
AS cust_name,
t.amount
FROM
transaction t
LEFT JOIN cust_type_1 c1 ON t.cust_id = c1.ct1_id
LEFT JOIN cust_type_2 c2 ON t.cust_id = c2.ct2_id

Upvotes: 0

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

You need to do your case in the select directly:

SELECT
  CASE
    WHEN t.type=1
    THEN cust1.ct1_name
    ELSE cust2.ct2_name
  END AS Customer_name ,
  t.amount
FROM transaction t
JOIN Cust_type_1 cust1
ON t.Cust_id = cust1.ct1_id
JOIN Cust_type_2 cust2
ON t.Cust_id = cust2.ct2_id;

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

Hope below snippet helps.

SELECT
  CASE
    WHEN T.TYPE = 1
    THEN
      (SELECT CTL_NAME FROM CUST_TYPE_1 A WHERE T.CUST_ID = A.CTL_ID
      )
    WHEN T.TYPE = 2
    THEN
      (SELECT CTL_NAME FROM CUST_TYPE_2 B WHERE T.CUST_ID = B.CTL_ID
      )
  END nme,
  T.AMOUNT
FROM transaction t;

Upvotes: 1

Related Questions