SQL Server 2019

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

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

INSERT INTO customer
    (account_code, tel1, tel2, tel3, address)
    VALUES
        ('CUST1', '123456789', 'thenumber', '987654321', 'address1'),
        ('CUST2', '123456789', 'bullshit', '987654321', 'address2'),
        ('CUST3', 'dummy', 'bullshit', '987654321', 'address2'),
        ('CUST4', '123456789', 'thenumber', '987654321', 'address4')
;

INSERT INTO contact
    (account_code, ctel1, ctel2, ctel3, cname)
    VALUES
        ('CUST1', '123456789', 'dummy', '987654321', 'cname11'),
        ('CUST1', '123456789', '987654321', 'bullshit', 'cname12'),
        ('CUST2', '123456789', 'thenumber', '987654321', 'cname21'),
        ('CUST2', '123456789', '987654321', 'thenumber', 'cname22'),
        ('CUST3', '123456789', 'dummy', '987654321', 'cname31'),
        ('CUST3', '123456789', '987654321', 'bullshit', 'cname32'),
        ('CUST4', '123456789', 'thenumber', '987654321', 'cname41'),
        ('CUST4', '123456789', '987654321', 'thenumber', 'cname42')
;
12 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
)
SELECT *
 FROM cust_check
 WHERE cust_match = 1


account_code
tel1
tel2
tel3
address
cust_match


CUST1
123456789
thenumber
987654321
address1
1

CUST4
123456789
thenumber
987654321
address4
1


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

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


account_code
nb_rows
nb_matched


CUST2
2
2

CUST4
2
2


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


CUST1
123456789
987654321
bullshit
cname12
address1

CUST1
123456789
dummy
987654321
cname11
address1

CUST2
123456789
987654321
thenumber
cname22
address2

CUST2
123456789
thenumber
987654321
cname21
address2

CUST4
123456789
987654321
thenumber
cname42
address4

CUST4
123456789
thenumber
987654321
cname41
address4
account_code	tel1	tel2	tel3	address	cust_match
CUST1	123456789	thenumber	987654321	address1	1
CUST4	123456789	thenumber	987654321	address4	1