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


  • id: NUMBER(9)

  • county: NUMBER(2)

  • date: DATE


  • 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?

  • I think I might need a Right Join but now sure how I can implement this.
    – Jose Ortiz
    Aug 31, 2018 at 21:45
  • DATE is a keyword. Don't use it as a Column Name. Sep 1, 2018 at 12:09
  • Like I said, it is just a toy example. These are not the actual tables. The actual structure are established.
    – Jose Ortiz
    Sep 1, 2018 at 21:23

1 Answer 1


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

---------- ---------- --------
         1         10

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

---------- ---------- --------
         2         20 222 twix

  • I kept iterating through many solutions but since in my case, there are multiple changes per day per id, this solution will not work correctly. However, in the cases that there is only a single row, this solution is perfect, therefore, I will mark this as the solution. Thank you very much for your help. I was forced to find a compromise for the solution but your solution actually help me out. Thank you again.
    – Jose Ortiz
    Sep 5, 2018 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.