CODEWITHSUNDEEP
sqlms-accesscount
David Tan
David Tan

Reputation: 23

Count similar records

How do I write an SQL showing the number of films member 1 and 2 have in common?

MID=Member ID FID=Film ID Explanation of table below: Member 1 has films 2,3,5,17,21 on his list. Member 2 has films 5,14,18 on his list. 

 1. MID ------ | FID
 2. M000001 | F000002
 3. M000001 | F000003
 4. M000001 | F000005
 5. M000001 | F000021
 6. M000002 | F000005
 7. M000002 | F000014
 8. M000002 | F000018
 9. M000003 | F000001
 10. M000003 | F000004
 11. M000003 |F000024

*sorry for the poor table.

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you want the number for all pairs, just do a self join:

select t1.mid, t2.mid, count(*)
from table as t1 inner join
     table as t2
     on t1.fid = t2.fid and t1.mid < t2.mid
group by t1.mid, t2.mid;

If you want to limit this just to two members, you can do:

select count(*)
from table as t1 inner join
     table as t2
     on t1.fid = t2.fid and
        t1.mid = 1 and
        t2.mid = 2;

Upvotes: 3

wvdz
wvdz

Reputation: 16651

With a subquery is one way.

SELECT COUNT(*) FROM t1 t
WHERE EXISTS (SELECT 1 FROM t1 WHERE Mid = 2 AND Fid = t.Fid)
AND Mid = 1

Upvotes: 0

Related Questions