SQL group but return wrong result

Question

I have a table:

name	code
a	1
a	2
b	1
b	2
c	2
c	3

want to get this result where code =1 and group by name

name	code
a	1
a	2
b	1
b	2

I tried this query:

select name, code where code=1 and group by name , code

but I just get this:

name	code
a	1
b	1

How can I fix it to get the correct results?

Answer 1

You appear to want all of the records for names that contain a code = 1

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

fiddle

CTE version as suggested by Isolated

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