How to joins in oracle based on condition

I have two tables as below:

Table Apple:

+----------+----------+---------+
| APPLE_ID | PHONE_ID | IPAD_ID |
+----------+----------+---------+
|        1 |     1001 |    2001 |
|        2 |     1002 |    2002 |
|        3 |     1003 |    2003 |
|        4 |     1004 |    2004 |
+----------+----------+---------+

Table KEEY:

+---------+----------+--------+-----------+
| KEEY_ID |   NAME   | DTL_ID | DEVICE_ID |
+---------+----------+--------+-----------+
|       1 | PHONE_ID |   1001 |       111 |
|       2 | PHONE_ID |   2001 |       111 |
|       3 | IPAD_ID  |   2001 |       222 |
|       4 | PHONE_ID |   1003 |       444 |
|       5 | MAC_ID   |    367 |       333 |
+---------+----------+--------+-----------+

Desired Output:

+----------+----------+---------+-----------------+----------------+
| APPLE_ID | PHONE_ID | IPAD_ID | PHONE_DEVICE_ID | IPAD_DEVICE_ID |
+----------+----------+---------+-----------------+----------------+
|        1 |     1001 |    2001 |             111 | 222            |
|        3 |     1003 |    2003 |             444 | null           |
+----------+----------+---------+-----------------+----------------+

Code Tried So far:

SELECT 
APPLE.APPLE_ID,
APPLE.PHONE_ID,
APPLE.IPAD_ID,
NULL AS IPHONE_DEVICE_ID,
KY.DEVICE_ID AS IPAD_DEVICE_ID
FROM APPLE
LEFT JOIN KEEY KY ON APPLE.IPAD_ID=KY.DTL_ID WHERE KY.NAME='IPAD_ID'
UNION
SELECT 
APPLE.APPLE_ID,
APPLE.PHONE_ID,
APPLE.IPAD_ID,
KY.DEVICE_ID AS PHONE_DEVICE_ID,
NULL AS IPAD_DEVICE_ID
FROM APPLE
LEFT JOIN KEEY KY ON APPLE.PHONE_ID=KY.DTL_ID WHERE KY.NAME='PHONE_ID'

This is giving me :

+----------+----------+---------+------------------+----------------+
| APPLE_ID | PHONE_ID | IPAD_ID | IPHONE_DEVICE_ID | IPAD_DEVICE_ID |
+----------+----------+---------+------------------+----------------+
|        1 |     1001 |    2001 | 111              | (null)         |
|        1 |     1001 |    2001 | (null)           | 222            |
|        3 |     1003 |    2003 | 444              | (null)         |
+----------+----------+---------+------------------+----------------+

I guess i need to use pivot instead of Union to get both the ids on same row.

Have you ever encountered such scenarios? Any pointers to proceed will be very helpful.

Thanks in Advance!

DDL used for the above problem:

CREATE TABLE APPLE 
( APPLE_ID INTEGER,
 PHONE_ID INTEGER,
 IPAD_ID INTEGER);
 
 INSERT INTO APPLE VALUES (1,1001,2001);
 INSERT INTO APPLE VALUES (2,1002,2002);
 INSERT INTO APPLE VALUES (3,1003,2003);
 INSERT INTO APPLE VALUES (4,1004,2004);
 
 CREATE TABLE KEEY 
( KEEY_ID INTEGER,
 NAME VARCHAR2(50),
 DTL_ID INTEGER,
 DEVICE_ID INTEGER);
 
 INSERT INTO KEEY VALUES (1,'PHONE_ID',1001,111);
 INSERT INTO KEEY VALUES (2,'PHONE_ID',2001,111);
 INSERT INTO KEEY VALUES (3,'IPAD_ID',2001,222);
 INSERT INTO KEEY VALUES (4,'PHONE_ID',1003,444);
 INSERT INTO KEEY VALUES (5,'MAC_ID',367,333);

Answer

I think you just want two joins:

select a.*, k1.device_id as phone_id, d2.device_id as ipad_id
from apple a join
     keey k1
     on a.phone_id = k1.dtl_id and k1.name = 'PHONE_ID' left join
     keey k2
     on a.ipad_id = k2.dtl_id and k2.name = 'IPAD_ID';

Here is a db<>fiddle (it uses Postgres just because that is easier to set up in the fiddle, but the results should be the same).

Advertisement

Answer