add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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'),
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 (
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