SQL Server 2022

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

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE Example (
    name	VARCHAR(512),
    code	VARCHAR(512)
);

INSERT INTO Example (name, code) VALUES
	('a', '1'),
	('a', '2'),
	('b', '1'),
	('b', '2'),
	('c', '2'),
	('c', '3');

SELECT * FROM example


name
code


a
1

a
2

b
1

b
2

c
2

c
3


SELECT *
FROM Example 
WHERE name in
(SELECT name from Example WHERE Code='1')


name
code


a
1

a
2

b
1

b
2


WITH CTE as
(
  SELECT * FROM EXAMPLE WHERE Code=1
)
SELECT E.*
FROM CTE C
INNER JOIN Example E ON C.name=E.name 


name
code


a
1

a
2

b
1

b
2