For example:
I want to pass in a tax id number to find all accounts associated with this tax id. This would simply be:
SELECT TAX_ID, ACCOUNT_NUMBER FROM CUSTOMER_RECORDS
I would then have a list of all accounts. However, each account could also have other tax ids associated to it (co-borrower, guarantors, etc). So I want to take the returned accounts and then find all tax ids associated to them. I then need to repeat these steps to find all accounts associated to all of the new tax ids that were returned.
The end result would be a list of accounts and tax ids that all form one "relationship".
I was thinking that a recursive CTE may work for this scenario? However, this is a bit above my skill level in SQL. I would greatly appreciate any assistance.
Please try the following:
with customer_records as
(
select 111 tax_id, 0 account_number from dual union
select 1 , 100 from dual union
select 1 , 200 from dual union
select 1 , 300 from dual union
select 1 , 400 from dual union
select 2 , 100 from dual union
select 3 , 202 from dual union
select 4 , 303 from dual union
select 5 , 400 from dual union
select 0 , 222 from dual
)
SELECT c1.TAX_ID, c1.ACCOUNT_NUMBER
FROM CUSTOMER_RECORDS c1
where 1=1
and c1.tax_id = :tax_id
union
SELECT c2.TAX_ID, c2.ACCOUNT_NUMBER
FROM CUSTOMER_RECORDS c1
, CUSTOMER_RECORDS c2
where 1=1
and c1.tax_id = :tax_id
and c1.tax_id <> c2.tax_id
and c1.account_number = c2.account_number
;