Converts a query which uses exists/not exists to a three table join

ANAND 0
Viewed 38

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:

id phone_number account_name
123 80001 1001

Table_B

id phone_number account_name
124 80002 1002

Table_C

id phone_number account_name
125 80003 1003

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!

plsql oracle sql
1 Answers

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;
Thorsten Kettner0
Related