CODEWITHSUNDEEP
sqlsql-servert-sql
Chris Koe
Chris Koe

Reputation: 3

Count elements in table a that have two exactly matching elements in table b

I have two tables and need to get a count of all entries from table A that have two specific matches in table B. Table B has tables A's Id as a foreign key.

Table a
ID       Name
1        Foo
2        Bar
3        John
4        Jane

Table b
aID     Value
1       12
1       12
2       8
3       8
3       12
4       12
4       8

I now need a count of all names in table A that have both value 8 AND 12 in table B at least once.

SELECT COUNT(*) FROM a join b on a.id = b.aId where b.value = 8 and b.value = 12

gets me 0 results. The correct result should be 2 (John and Jane).

edit: Obviously, @Larnu is correct and 8 will never be 12.

Also, I should have clarified that there can be two or more of a single value in table B for any table A id, but none of the other (e.g. 8 twice but no 12). I updated the table to reflect that.

Upvotes: 0

Views: 56

Answers (3)

T N
T N

Reputation: 10205

Joining is not the answer here. You need a WHERE clause that includes a correlated subquery that checks your condition using COUNT() or EXISTS(). One of the following should do.

SELECT COUNT(*) FROM A
WHERE (SELECT COUNT(*) ​FROM B ​WHERE B.aID = A.ID ​AND B.VALUE IN (8, 12)) = 2

SELECT COUNT(*) FROM A
WHERE EXISTS(SELECT * FROM B WHERE B.aID = A.ID AND B.VALUE = 8)
AND EXISTS(SELECT * FROM B WHERE B.aID = A.ID AND B.VALUE = 12)

Upvotes: 0

Zakaria
Zakaria

Reputation: 4796

A subquery to get the number of times 8 and 12 appear for each row will do the trick:

select count(id) from 
(select id, sum(case when b.Value = 8 then 1 else 0 end) as ct8,
sum(case when b.Value = 12 then 1 else 0 end) as ct12
from a inner join b on a.id = b.aID
group by a.id) as t
where ct8 >= 1 and ct12 >=1

Fiddle

Upvotes: 0

sagi
sagi

Reputation: 40491

You can use EXISTS and HAVING:

SELECT COUNT(*) FROM a
WHERE EXISTS(SELECT b.aID FROM b
             WHERE a.ID = b.aID
             GROUP BY b.aID
             HAVING COUNT(*) = 2)

If you want specifically value = 8 or 12 then add AND b.value IN(8,12) to the inner query

Upvotes: 1

Related Questions