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

Related posts about sql

Related posts about query