How do I apply use Full Join without creating nulls?-CodePudding

I have a bit strange requirement in Oracle 11c. I have the below 3 tables where I need to join these 3 tables based on CUSTOMER_ID column and show dates without creating duplicate rows.

When I use the FULL join, I am not getting the expected output. ie, in the last row, CUSTOMER_ID column is null.

The expected output is like below. ie, even if DEV_DATES has more rows or TEST_DATES has more rows, it should show all dates based on the CUSTOMER_ID.

Note that there is no relation between the dates in the DEV_DATES and TEST_DATES values. The relation is only with the CUSTOMER_ID. For getting dates in the same row, I used ROW_NUMBER() function.

For your easy execution, I have attached sample sql below.

WITH CUSTOMERS AS
(
    SELECT 1 AS CUSTOMER_ID FROM DUAL
    UNION
    SELECT 2 AS CUSTOMER_ID FROM DUAL
)
,DEV_DATES AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY DEV_RUN_DATE) AS RNO, T.*
    FROM
    (
        SELECT 1 AS CUSTOMER_ID, TO_DATE('5 Jan 2017', 'DD MON YYYY') AS DEV_RUN_DATE FROM DUAL
        UNION
        SELECT 1 AS CUSTOMER_ID, TO_DATE('12 Jan 2017', 'DD MON YYYY') AS DEV_RUN_DATE FROM DUAL        
    ) T
)
,TEST_DATES AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY CUSTOMER_ID ORDER BY TEST_RUN_DATE) AS RNO, T.*
    FROM
    (
        SELECT 1 AS CUSTOMER_ID, TO_DATE('8 Jan 2017', 'DD MON YYYY') AS TEST_RUN_DATE FROM DUAL
        UNION
        SELECT 1 AS CUSTOMER_ID, TO_DATE('23 Jan 2017', 'DD MON YYYY') AS TEST_RUN_DATE FROM DUAL
        UNION
        SELECT 1 AS CUSTOMER_ID, TO_DATE('27 Jan 2017', 'DD MON YYYY') AS TEST_RUN_DATE FROM DUAL
    ) T
)
SELECT DISTINCT CUST.CUSTOMER_ID,DEV.DEV_RUN_DATE,TEST.TEST_RUN_DATE
FROM CUSTOMERS CUST
LEFT JOIN DEV_DATES DEV ON DEV.CUSTOMER_ID = CUST.CUSTOMER_ID
FULL JOIN TEST_DATES TEST ON TEST.CUSTOMER_ID = DEV.CUSTOMER_ID AND DEV.RNO = TEST.RNO
ORDER BY CUST.CUSTOMER_ID

Appreciate if anyone can help on the resolution.

Please let me know if you have any questions.

CodePudding user response：

Use COALESCE and select the first non null CUSTOMER_ID:

SELECT DISTINCT
    COALESCE(CUST.CUSTOMER_ID, TEST.CUSTOMER_ID) AS CUSTOMER_ID,
    DEV.DEV_RUN_DATE,
    TEST.TEST_RUN_DATE
FROM CUSTOMERS CUST
LEFT JOIN DEV_DATES DEV ON DEV.CUSTOMER_ID = CUST.CUSTOMER_ID
FULL JOIN TEST_DATES TEST
    ON TEST.CUSTOMER_ID = DEV.CUSTOMER_ID AND DEV.RNO = TEST.RNO
ORDER BY CUST.CUSTOMER_ID;

CodePudding user response：

This?

SQL> with customers as
  2  (
  3      select 1 as customer_id from dual
  4      union
  5      select 2 as customer_id from dual
  6  )
  7  ,dev_dates as
  8  (
  9      select row_number() over(partition by customer_id order by dev_run_date) as rno, t.*
 10      from
 11      (
 12          select 1 as customer_id, to_date('5 Jan 2017', 'DD MON YYYY') as dev_run_date from dual
 13          union
 14          select 1 as customer_id, to_date('12 Jan 2017', 'DD MON YYYY') as dev_run_date from dual
 15      ) t
 16  )
 17  ,test_dates as
 18  (
 19      select row_number() over(partition by customer_id order by test_run_date) as rno, t.*
 20      from
 21      (
 22          select 1 as customer_id, to_date('8 Jan 2017', 'DD MON YYYY') as test_run_date from dual
 23          union
 24          select 1 as customer_id, to_date('23 Jan 2017', 'DD MON YYYY') as test_run_date from dual
 25          union
 26          select 1 as customer_id, to_date('27 Jan 2017', 'DD MON YYYY') as test_run_date from dual
 27      ) t
 28  )

Query begins here:

 29  select
 30    c.customer_id,
 31    d.dev_run_date,
 32    t.test_run_date
 33  from customers c
 34  full join test_dates t on t.customer_id = c.customer_id
 35  full join dev_dates  d on d.customer_id = c.customer_id and d.rno = t.rno
 36  order by 1, 2, 3;

CUSTOMER_ID DEV_RUN_DA TEST_RUN_D
----------- ---------- ----------
          1 2017-01-05 2017-01-08
          1 2017-01-12 2017-01-23
          1            2017-01-27
          2

SQL>