title: How to joins in oracle based on condition
body: <p>I have two tables as below:</p>
  <p>Table Apple:</p>
  <pre><code>+----------+----------+---------+
  | APPLE_ID | PHONE_ID | IPAD_ID |
  +----------+----------+---------+
  |        1 |     1001 |    2001 |
  |        2 |     1002 |    2002 |
  |        3 |     1003 |    2003 |
  |        4 |     1004 |    2004 |
  +----------+----------+---------+
  </code></pre>
  <p>Table KEEY:</p>
  <pre><code>+---------+----------+--------+-----------+
  | 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 |
  +---------+----------+--------+-----------+
  </code></pre>
  <p>Desired Output:</p>
  <pre><code>+----------+----------+---------+-----------------+----------------+
  | APPLE_ID | PHONE_ID | IPAD_ID | PHONE_DEVICE_ID | IPAD_DEVICE_ID |
  +----------+----------+---------+-----------------+----------------+
  |        1 |     1001 |    2001 |             111 | 222            |
  |        3 |     1003 |    2003 |             444 | null           |
  +----------+----------+---------+-----------------+----------------+
  </code></pre>
  <p>Code Tried So far:</p>
  <pre><code>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'
  </code></pre>
  <p>This is giving me :</p>
  <pre><code>+----------+----------+---------+------------------+----------------+
  | 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)         |
  +----------+----------+---------+------------------+----------------+
  </code></pre>
  <p>I guess i need to use pivot instead of Union to get both the ids on same row.</p>
  <p>Have you ever encountered such scenarios? Any pointers to proceed will be very helpful.</p>
  <p>Thanks in Advance!</p>
  <p>DDL used for the above problem:</p>
  <pre><code>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);
  </code></pre>