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?
>Solution :
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')
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