This is the first time I've asked a question. It's the first time I've not been able to find what I'm looking for on SO and I think I'm going mad because it should be simple!
Suppose you have a MySQL table, 'T' with two columns 'A', 'B'.
A B
---
1 2
2 1
1 3
3 1
2 4
I'd like to count mutual relations given a variable. So if I supply
X = 1
It should return
2
Because 1, 2 AND 2, 1 exist, and 1, 3 AND 3, 1 exist. However, supply
X = 2
It should return
1
Because 1, 2 AND 2, 1 exists, but no other rows where 2 = n AND n = 2.
I hope this is clear! And apologies if it's simple, feel like I'm on a brain freeze.. Cheers!
This will work (but if you have a row with (n,n) it will also count as a mutual relation):
select count(*) from T t1
join T t2 on t1.B = t2.A
where t1.A = ? and t2.B = ?
(pass the same value in for both parameters)