sql combine two subqueries
Posted
by Claudiu
on Stack Overflow
See other posts from Stack Overflow
or by Claudiu
Published on 2010-06-11T21:08:26Z
Indexed on
2010/06/11
21:13 UTC
Read the original article
Hit count: 219
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?
© Stack Overflow or respective owner