I am working on a problem statement where I need to retain records based on a logic which is being applied on three tables.The tables are listed as below.

Table_A:

Table_B

Table_C

I wrote a query as shown below:

select /* PARALLEL(su,8)*/ su.ID from TableA su where
(
EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ PHONE_NUMBER from TableB sa where PHONE_NUMBER=su.PHONE_NUMBER)
or EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ PHONE_NUMBER from TableC sa where PHONE_NUMBER=su.PHONE_NUMBER)
)
and
(
EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ ACCOUNT_NAME from TableB sa where ACCOUNT_NAME=su.ACCOUNT_NAME)
or EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ ACCOUNT_NAME from TableC sa where ACCOUNT_NAME=su.ACCOUNT_NAME)
)
and
NOT EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ ID from TableC sa where ID=su.ID)
and NOT EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ ID from TableB sa where ID=su.ID);

My requirement here is that for a record of table A if the phone_number and account_name are either in TableB or TableC and the ID is not present in any of these two tables then the record should be present.The existing code works in most of the scenarios but there is a specific scenario where it is not working where for a specific record of table A the acccount_name and phone_number is present for two different records in either of TableB or TableC.I want to exclude these kind of records from my output hence I tried converting my query to a three way join so that the join in on specific records. The query which I wrote is as below-

select su.id from TableA su,TableB sa,TableC sb where (su.PHONE_NUMBER=sa.PHONE_NUMBER OR su.PHONE_NUMBER=sb.PHONE_NUMBER) and (su.ACCOUNT_NAME=sa.ACCOUNT_NAME OR su.ACCOUNT_NAME=sb.ACCOUNT_NAME) 
and NOT EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ id from TableC sa where id=su.id)
and NOT EXISTS (select /*  PARALLEL(sa,8) PARALLEL(su,8)*/ id from TableB sa where id=su.id);

This query is returning a number of duplicates and I tried using distinct as well but I am still getting incorrect results.

Could anyone suggest what is wrong on my query? Please advice.

Thank you!

CodePudding user response：

I am still not sure if I understand what you require. Here is a query that finds TableA rows where there exists a row in TableB or TableC with the same ACCOUNT_NAME / PHONE_NUMBER pair but a different ID.

select *
from tablea a
where exists
(
  select null
  from tableb b
  where b.phone_number = a.phone_number
    and b.account_name = a.account_name
    and b.id <> a.id
)
or exists
(
  select null
  from tablec c
  where c.phone_number = a.phone_number
    and c.account_name = a.account_name
    and c.id <> a.id
)
order by id;

If you want to see the other IDs, you must join instead. I suggest a union of TableB and TableC for this:

select a.*, bc.table_name, bc.id as other_table_id
from tablea a
join
(
  select 'TableB' as table_name, id, phone_number, account_name from tableb
  union all
  select 'TableC' as table_name, id, phone_number, account_name from tablec
) bc on bc.phone_number = a.phone_number
    and bc.account_name = a.account_name
    and bc.id <> a.id
order by a.id, bc.table_name, bc.id;