How to get a selection from a join, where there is no result in one of the tables

join;oracleoracle-11g

I am working in an oracle database and looking to get some queries to identify any changes of address from a customer. I am creating a report from the results in a daily batch process. I will simplify the table structure as an example.

I have 2 tables I am working with. Each is an audit table which keeps track of changes such as updates and inserts via a trigger. Each table also has a column to store the date of change.

One table contains a county code, the other contains the street address: t_county and t_address

t_county

  • id: NUMBER(9)

  • county: NUMBER(2)

  • date: DATE

t_address

  • id: NUMBER(9)

  • address: VARCHAR2(60)

  • date: DATE

I need to build a query which selects the county and address if either one has a row on a specific day.

Say there is a change of address on a specific day, but there is no change in the county. I would like for the query to have a result such as below

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  NULL   |123 test|
+---------+---------+--------+

If there is a change of county but the address is the same: 

+---------+---------+--------+
|    ID   |  County | Address|
+---------+---------+--------+
|     1   |  32     |  NULL  |
+---------+---------+--------+

How can I build such a query?

Best Answer

Here's one option, which uses UNION of those tables. Aggregate (MAX) is used to display changes in both county and address in a single row; otherwise, you'd get two separate rows. This won't work correctly if there are several changes within a single date.

SQL> with t_county (id, county, cdate) as
  2    (select 1, 10, date '2018-08-02' from dual union all
  3     select 2, 20, date '2018-08-05' from dual
  4    ),
  5  t_address (id, address, cdate) as
  6    (select 1, '123 test', date '2018-08-10' from dual union all
  7     select 2, '222 twix', date '2018-08-05' from dual
  8    ),
  9  -- union of those tables
 10  t_union as
 11    (select id, county     , null address, cdate from t_county union all
 12     select id, null county, address     , cdate from t_address
 13    )
 14  select id,
 15    max(county) county,
 16    max(address) address
 17  from t_union
 18  where cdate = to_date('&par_date', 'yyyy-mm-dd')
 19  group by id;
Enter value for par_date: 2018-08-02

        ID     COUNTY ADDRESS
---------- ---------- --------
         1         10

SQL> /
Enter value for par_date: 2018-08-05

        ID     COUNTY ADDRESS
---------- ---------- --------
         2         20 222 twix

SQL>
Related Question