Does a Self Join or EXISTS Help here?

I’m Using Advantage Server 12. I need a result set that finds all the records when the supplied parameter occurs in either of a master or a detail table when the supplied value is not the linking field.

Customer Table

  |AccountCode |Tel1|Tel2|Tel3|Accout Name/Address etc....
  |   ACODE    |    |    |    |

N < 2

Contact Table

  |AccountCode |cTel1|cTel2|cTel3|ContactName/Address etc....
  |   ACODE    |     |     |     |

N >=0

Require the Row from Customer and All rows with matching ACODE from Contacts. When Tel1 or Tel2 or Tel3 or cTel1 or cTel2 or cTel3 is supplied

This SQL gives the required result when the supplied value (‘thenumber’) is found in any of the Customer fields When the supplied number is found in the contact table it returns the Customer fields and only data from the matching row in Contacts

declare @Tel string;

set @Tel = 'thenumber';
      
 select
  @TEL as calling,c.Tel1,c.Tel2,c.contact as "Primary",
  c.acnt_nmbr,c.Acnt_name, a.contact,a.cTel1 as telephone1,a.cTel2 as telephone2
 from
  customer c 
 full outer join contact a on (c.acnt_nmbr=a.acnt_nmbr)
 where
  replace(c.Tel1,' ','') = @Tel
   or
  replace(c.Tel2,' ','') = @Tel 
   or
  replace(c.Tel3,' ','') = @Tel
   or 
  replace(a.cTel1,' ','') = @Tel
   or
  replace(a.cTel2,' ','') = @Tel 
   or
  replace(a.cTel3,' ','') = @Tel

Result when ‘thenumber’ is found in Customer – This is what we want

calling   Tel1     Tel2      Primary  acnt_nmbr Acnt_name contact telephone1 telephone2 
thenumber 11111111 thenumber KIERAN   687       theCo     Pat     12234560   333444555  
thenumber 11111111 thenumber KIERAN   687       theco     Mary    45678900   444555666  
thenumber 11111111 thenumber KIERAN   687       theco     Jon     22233344  
thenumber 11111111 thenumber KIERAN   687       theco     Paul    22244455   124578111  
thenumber 11111111 thenumber KIERAN   687       theco     Jane    33225544  

Result when ‘thenumber’ is found in Contacts – We want the same result set as above

calling   Tel1      Tel2      Primary  acnt_nmbr Acnt_name  contact  telephone1 telephone2  
thenumber 11111111  2222222   KIERAN   687       theco      Jane     thenumber  

I’m thinking either some self join or EXISTS statement is the answer but not sure how to proceed.

Answer

This should achieve what you want:

 select
  @TEL as calling,c.Tel1,c.Tel2,c.contact as "Primary",
  c.acnt_nmbr,c.Acnt_name, a.contact,a.cTel1 as telephone1,a.cTel2 as telephone2
 from
  customer c 
 left join contact a on (c.acnt_nmbr=a.acnt_nmbr)
 WHERE c.acnt_nmbr IN
 (
 SELECT DISTINCT acnt_nmbr FROM 
  (SELECT acnt_nmbr, Tel1, Tel2
   FROM Customer
   UNION
   SELECT acnt_nmbr, cTel1, cTel2
   FROM Contact) x
  WHERE
   replace(x.Tel1,' ','') = @Tel
   or
  replace(x.Tel2,' ','') = @Tel
 ) 

(I’ll leave you to work out how to add the Tel3 🙂

Note that I have changed your full outer join to a simple left join because the code as presented will not work with customerless contacts.

If you really need a full outer join, the code becomes a bit more convoluted:

SELECT z.* FROM
( select @TEL as calling,c.Tel1,c.Tel2,c.contact as "Primary",
  COALESCE(c.acnt_nmbr,a.acnt_nmbr) AS AccountNo, 
  c.Acnt_name, a.contact,a.cTel1 as telephone1,a.cTel2 as telephone2
 from
  Customer c 
 full outer join Contact a on (c.acnt_nmbr=a.acnt_nmbr)
) z
WHERE COALESCE(z.AccountNo,0) IN
(...)

I have modified Christophe’s DB-fiddle to show this working.

Note that the solution for the full outer join is slightly different in the fiddle, to cope with the different behaviour of Microsoft SQL server compared to the Advantage database server. (With ADS DBF tables, NULL values in an integer field become zeros in the UNION for example).