Select rows with matching columns and all columns match a value for id

Question

I want to select the distinct id from table 1 that matches the id and gen_id of table 2 only if the Status column results are all 'A' for that id.

TABLE_1

ID	Gen_ID
1	21
1	24
2	23
2	25
3	28
3	29
3	31
4	32
4	33

TABLE_2

ID	Gen_ID	Status
1	21	A
1	24	A
2	23	A
2	25	B
3	28	A
3	29	B
3	31	C
4	32	A
4	33	A

Desired result:

ID
1
4

I tried the following:

SELECT DISTINCT a.id
FROM TABLE_1 a
inner join TABLE_2 b on a.id = b.id and a.Gen_ID = b.Gen_ID and b.status ='A'
group by TABLE_1.ID
having SUM(case when b.status = 'A' then 1 else 0 end) = count(*);

But that seems to return any record with 'A' as Status. Actual result:

ID
1
2
3
4

What am I doing wrong?

Answer 1

Just remove b.status ='A' from the join :

SELECT a.ID, count(1) as totalCount, SUM(case when b.status = 'A' then 1 else 0 end) A_count
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID

will return :

ID  TOTALCOUNT  A_COUNT
1   2   2
2   2   1
3   3   1
4   2   2

Then a comparison of total count with the count where status = 'A' will give us the expected output:

SELECT a.ID
FROM TABLE_1 a
inner join TABLE_2 b on a.ID = b.ID and a.Gen_ID = b.Gen_ID
group by a.ID
having SUM(case when b.status = 'A' then 1 else 0 end) = count(1)

Return :

ID
1
4