sql combine two subqueries
- by Claudiu
I have two tables. Table A has an id column. Table B has an Aid column and a type column. Example data:
A: id
--
1
2
B: Aid | type
----+-----
1 | 1
1 | 1
1 | 3
1 | 1
1 | 4
1 | 5
1 | 4
2 | 2
2 | 4
2 | 3
I want to get all the IDs from table A where there is a certain amount of type 1 and type 3 actions. My query looks like this:
SELECT id
FROM A
WHERE (SELECT COUNT(type)
FROM B
WHERE B.Aid = A.id
AND B.type = 1) = 3
AND (SELECT COUNT(type)
FROM B
WHERE B.Aid = A.id
AND B.type = 3) = 1
so on the data above, just the id 1 should be returned.
Can I combine the 2 subqueries somehow?
