SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (
    id int,
    age int
);
INSERT INTO table1 (id, age)
VALUES (1, 23),
       (2, 25),
       (3, 37), 
       (4, 43), 
       (5, 57);


CREATE TABLE table2 (
    id int,
    id1 int,
    operation varchar(1)
);
INSERT INTO table2 (id, id1, operation)
VALUES (1, 1, 'A'),
       (NULL, 1, 'B'), 
       (NULL, 1, 'C'), 
       (2, 2, 'A'),
       (2, 2, 'B'),
       (3, 3, 'A'), 
       (4, 4, 'A'), 
       (NULL, 4, 'B'), 
       (5, 5, 'A');

CREATE TABLE table3 (
    id int,
    id1 int,
    ops varchar(1)
);
INSERT INTO table3 (id, id1, ops)
VALUES (1, 1, 'q'),
       (NULL, 1, 'w'), 
       (NULL, 1, 'e'), 
       (2, 2, 'q'),
       (NULL, 2, 'w'), 
       (3, 3, 'q'), 
       (4, 4, 'q'), 
       (NULL, 4, 'w'), 
       (NULL, 5, 'q'); 
23 rows affected

SELECT t1.id AS t1id, 
  t1.age AS t1age, 
  t2.id AS t2id, 
  t2.id1 AS t2id1, 
  t2.operation AS t2operation, 
  t3.id AS t3id, 
  t3.id1 AS t3id1, 
  t3.ops AS t3ops
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3
ON COALESCE(t3.id, t3.id1) = t1.id


t1id
t1age
t2id
t2id1
t2operation
t3id
t3id1
t3ops


1
23
1
1
A
1
1
q

1
23
1
1
A
null
1
w

1
23
1
1
A
null
1
e

1
23
null
1
B
1
1
q

1
23
null
1
B
null
1
w

1
23
null
1
B
null
1
e

1
23
null
1
C
1
1
q

1
23
null
1
C
null
1
w

1
23
null
1
C
null
1
e

2
25
2
2
A
2
2
q

2
25
2
2
A
null
2
w

2
25
2
2
B
2
2
q

2
25
2
2
B
null
2
w

3
37
3
3
A
3
3
q

4
43
4
4
A
4
4
q

4
43
4
4
A
null
4
w

4
43
null
4
B
4
4
q

4
43
null
4
B
null
4
w

5
57
5
5
A
null
5
q


SELECT t1.id, t1.age, t2.operation, t3.ops
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON COALESCE(t2.id, t2.id1) = t1.id 
LEFT JOIN table3 AS t3
ON COALESCE(t3.id, t3.id1) = t1.id
WHERE EXISTS 
  (SELECT 1 
  FROM table3 t
  WHERE id = t1.id
  AND (t3.ops = 'w'))

-- here ID 3 and 5 drop out, because column OPS contains no 'w'
-- but the remaining values of ops are not shown


id
age
operation
ops


1
23
A
w

1
23
B
w

1
23
C
w

2
25
A
w

2
25
B
w

4
43
A
w

4
43
B
w


SELECT *
FROM (
    SELECT t1.id AS t1id, 
        t1.age AS t1age, 
        t2.id AS t2id, 
        t2.id1 AS t2id1, 
        t2.operation AS t2operation, 
        t3.id AS t3id, 
        t3.id1 AS t3id1, 
        t3.ops AS t3ops,
        MAX(CASE WHEN t3.ops = 'w' then 1 else 0 end) OVER(PARTITION BY t1.id) has_ops_w
    FROM table1 AS t1
    LEFT JOIN table2 AS t2 ON COALESCE(t2.id, t2.id1) = t1.id 
    LEFT JOIN table3 AS t3 ON COALESCE(t3.id, t3.id1) = t1.id
) t
WHERE has_ops_w = 1


t1id
t1age
t2id
t2id1
t2operation
t3id
t3id1
t3ops
has_ops_w


1
23
1
1
A
1
1
q
1

1
23
1
1
A
null
1
w
1

1
23
1
1
A
null
1
e
1

1
23
null
1
B
1
1
q
1

1
23
null
1
B
null
1
w
1

1
23
null
1
B
null
1
e
1

1
23
null
1
C
1
1
q
1

1
23
null
1
C
null
1
w
1

1
23
null
1
C
null
1
e
1

2
25
2
2
A
2
2
q
1

2
25
2
2
A
null
2
w
1

2
25
2
2
B
2
2
q
1

2
25
2
2
B
null
2
w
1

4
43
4
4
A
4
4
q
1

4
43
4
4
A
null
4
w
1

4
43
null
4
B
4
4
q
1

4
43
null
4
B
null
4
w
1

t1id	t1age	t2id	t2id1	t2operation	t3id	t3id1	t3ops
1	23	1	1	A	1	1	q
1	23	1	1	A	null	1	w
1	23	1	1	A	null	1	e
1	23	null	1	B	1	1	q
1	23	null	1	B	null	1	w
1	23	null	1	B	null	1	e
1	23	null	1	C	1	1	q
1	23	null	1	C	null	1	w
1	23	null	1	C	null	1	e
2	25	2	2	A	2	2	q
2	25	2	2	A	null	2	w
2	25	2	2	B	2	2	q
2	25	2	2	B	null	2	w
3	37	3	3	A	3	3	q
4	43	4	4	A	4	4	q
4	43	4	4	A	null	4	w
4	43	null	4	B	4	4	q
4	43	null	4	B	null	4	w
5	57	5	5	A	null	5	q