select rows having condition met for group (without temporary table)

Having the table with 3 columns:

ID  category    flag
1       A       1
2       A       0
3       A       0
4       B       0
5       C       0

I want to select all rows that have flag = 1 at least once per category.

Expected results:

ID  category    flag
1       A       1
2       A       0
3       A       0

It can be solved using a temporary table like this:

select ID into #tempTable from someTable where flag = 1
select * from someTable join #tempTable on someTable.ID = #tempTable.ID

But I'd prefer a solution with grouping, which I struggle to came up with. Any help will be appreciated.

GROUP BY cannot be used alone because it only returns 1 row per group (category).

-
You can use a sub query with flag = 1 and INNER JOIN:

SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
    SELECT DISTINCT category FROM data WHERE flag = 1
) d2 
    ON d2.category = d1.category ;

-
You can use the EXISTS clause:

SELECT d.ID, d.category, d.flag
FROM data d
WHERE EXISTS (
    SELECT 1 FROM data WHERE flag = 1 AND category = d.category
) ;

-
You can use IN clause (although EXISTS is better):

SELECT d.ID, d.category, d.flag
FROM data d
WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;

-
You can also use CROSS APPLY with a sub query on flag = 1:

SELECT d.ID, d.category, d.flag
FROM data d
CROSS APPLY (
    SELECT TOP (1) category 
    FROM data 
    WHERE flag = 1 AND category = d.category
) ca ;

DISTINCT aren't needed if, for each category, only 1 row can have flag = 1.

Output:

ID  category    flag
1       A       1
2       A       0
3       A       0

StackExchange Database Administrators Q#144214, answer score: 16