SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customer (
    account_code INTEGER,
    tel1         NVARCHAR(20),
    tel2         NVARCHAR(20),
    tel3         NVARCHAR(20),
    address      NVARCHAR(50)
);

CREATE TABLE contact (
    account_code INTEGER,
    ctel1        NVARCHAR(20),
    ctel2        NVARCHAR(20),
    ctel3        NVARCHAR(20),
    cname        NVARCHAR(50)
);

INSERT INTO customer
    (account_code, tel1, tel2, tel3, address)
    VALUES
        (1, '123456789', 'thenumber', '987654321', 'address1'),
        (2, '123456789', 'bullshit', '987654321', 'address2'),
        (3, 'dummy', 'bullshit', '987654321', 'address2'),
        (4, '123456789', 'thenumber', '987654321', 'address4')
;

INSERT INTO contact
    (account_code, ctel1, ctel2, ctel3, cname)
    VALUES
        (999, '123456789', 'thenumber', '987654321', 'Invalid customer'),
        (NULL, '123456789', 'thenumber', '987654321', 'No customer'),
        (1, '123456789', 'dummy', '987654321', 'cname11'),
        (1, '123456789', '987654321', 'bullshit', 'cname12'),
        (2, '123456789', 'thenumber', '987654321', 'cname21'),
        (2, '123456789', '987654321', 'thenumber', 'cname22'),
        (3, '123456789', 'dummy', '987654321', 'cname31'),
        (3, '123456789', '987654321', 'bullshit', 'cname32'),
        (4, '123456789', 'thenumber', '987654321', 'cname41'),
        (4, '123456789', '987654321', 'thenumber', 'cname42')
;
14 rows affected

declare @Tel AS NVARCHAR(12) = 'thenumber';

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



account_code
ctel1
ctel2
ctel3
cname
address


1
123456789
987654321
bullshit
cname12
address1

1
123456789
dummy
987654321
cname11
address1

2
123456789
987654321
thenumber
cname22
address2

2
123456789
thenumber
987654321
cname21
address2

4
123456789
987654321
thenumber
cname42
address4

4
123456789
thenumber
987654321
cname41
address4


declare @Tel AS NVARCHAR(12) = 'thenumber';

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


calling
Tel1
Tel2
account_code
address
cname
telephone1
telephone2


thenumber
123456789
thenumber
1
address1
cname11
123456789
dummy

thenumber
123456789
thenumber
1
address1
cname12
123456789
987654321

thenumber
123456789
bullshit
2
address2
cname21
123456789
thenumber

thenumber
123456789
bullshit
2
address2
cname22
123456789
987654321

thenumber
123456789
thenumber
4
address4
cname41
123456789
thenumber

thenumber
123456789
thenumber
4
address4
cname42
123456789
987654321


declare @Tel AS NVARCHAR(12) = 'thenumber';

SELECT x.*
FROM
( SELECT account_code, Tel1, Tel2
   FROM Customer
   UNION
   SELECT account_code, cTel1, cTel2
   FROM Contact) x
  WHERE
   replace(x.Tel1,' ','') = @Tel
   or
  replace(x.Tel2,' ','') = @Tel


account_code
Tel1
Tel2


null
123456789
thenumber

1
123456789
thenumber

2
123456789
thenumber

4
123456789
thenumber

999
123456789
thenumber


declare @Tel AS NVARCHAR(12) = 'thenumber';

SELECT z.* FROM
( select
  @TEL as calling,c.Tel1,c.Tel2,
  COALESCE(c.account_code,a.account_code) AS AccountNo,c.address, a.cname,a.cTel1 as telephone1,a.cTel2 as telephone2
 from
  customer c 
 full outer join contact a on (c.account_code=a.account_code)
 ) z
 WHERE COALESCE(z.AccountNo,-1) IN
 (
 SELECT DISTINCT account_code FROM 
  (SELECT account_code, Tel1, Tel2
   FROM Customer
   UNION
   SELECT COALESCE(account_code,-1), cTel1, cTel2
   FROM Contact) x
  WHERE
   replace(x.Tel1,' ','') = @Tel
   or
  replace(x.Tel2,' ','') = @Tel
 ) 


calling
Tel1
Tel2
AccountNo
address
cname
telephone1
telephone2


thenumber
123456789
thenumber
1
address1
cname11
123456789
dummy

thenumber
123456789
thenumber
1
address1
cname12
123456789
987654321

thenumber
123456789
bullshit
2
address2
cname21
123456789
thenumber

thenumber
123456789
bullshit
2
address2
cname22
123456789
987654321

thenumber
123456789
thenumber
4
address4
cname41
123456789
thenumber

thenumber
123456789
thenumber
4
address4
cname42
123456789
987654321

thenumber
null
null
999
null
Invalid customer
123456789
thenumber

thenumber
null
null
null
null
No customer
123456789
thenumber
account_code	ctel1	ctel2	ctel3	cname	address
1	123456789	987654321	bullshit	cname12	address1
1	123456789	dummy	987654321	cname11	address1
2	123456789	987654321	thenumber	cname22	address2
2	123456789	thenumber	987654321	cname21	address2
4	123456789	987654321	thenumber	cname42	address4
4	123456789	thenumber	987654321	cname41	address4
account_code	Tel1	Tel2
null	123456789	thenumber
1	123456789	thenumber
2	123456789	thenumber
4	123456789	thenumber
999	123456789	thenumber