Does a Self Join or EXISTS Help here?

Question

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 1

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).

Answer 2

It's a bit more tricky than you seem to expect and as far as I just see your latest comment, I hope the solution I will propose you will work (I do not know SAP technical environment at all).

On a SQL point of you, my approach is the following.
For both customer and contact, find matching values. It's quite easy for customer, but for contacts you need to identify single matching values, then keep contacts where all values are matching
Then, build the assembly of customer and contacts.
Finally, union results from both first filters, joined with the basis data.

Here is the final request I propose:

with cust_check as (
    SELECT account_code, tel1, tel2, tel3, address,
           CASE WHEN tel1 = @Tel THEN 1
                ELSE CASE WHEN tel2 = @Tel THEN 1
                          ELSE CASE WHEN tel3 = @Tel THEN 1
                                    ELSE 0
                               END
                     END
           END as cust_match
    from customer
),
cust_filter as (
    SELECT account_code, tel1, tel2, tel3, address AS detail 
     FROM cust_check
    WHERE cust_match = 1
),
contact_check as (
    SELECT account_code, ctel1, ctel2, ctel3, cname,
           CASE WHEN ctel1 = @Tel THEN 1
                ELSE CASE WHEN ctel2 = @Tel THEN 1
                          ELSE CASE WHEN ctel3 = @Tel THEN 1
                                    ELSE 0
                               END
                     END
           END as contact_match
    FROM contact
),
contact_filter as (
    SELECT account_code, count(*) as nb_rows, sum(contact_match) as nb_matched
     FROM contact_check
    GROUP BY account_code
    HAVING count(*) = sum(contact_match)
),
all_contacts as (
    SELECT t.account_code, ctel1, ctel2, ctel3, cname, address
      FROM contact as t
      JOIN customer as c ON c.account_code = t.account_code
),
union_match as 
(
    SELECT c.account_code, c.ctel1, c.ctel2, c.ctel3, c.cname, c.address
      FROM all_contacts c
      JOIN cust_filter f ON f.account_code = c.account_code
    UNION
    SELECT c.account_code, c.ctel1, c.ctel2, c.ctel3, c.cname, c.address
      FROM all_contacts c
      JOIN contact_filter f ON f.account_code = c.account_code
)
SELECT account_code, ctel1, ctel2, ctel3, cname, address
  FROM union_match

This request is based on MS SQL Server and you can easily 'play' with it thanks to DB-Fiddle - I just hope it will be compliant with your constraints!