I am busy trying to create a query that returns the number of mutual friends between users in a userlist. I have tried a few but they haven't been working as I had hoped, here is one of the queries I have tried
query("SELECT * FROM userlist WHERE userid = '$userid' AND status ='friend' AND friendid != '$userid' ");
the sql table below renders the relationships between users.
userid | friendid | type | status |
-----------------------------------
1 |2 | buddy | friend |
-----------------------------------
1 |3 | buddy | friend |
-----------------------------------
2 |3 | buddy | friend |
-----------------------------------
3 |2 | buddy | friend |
-----------------------------------
4 |2 | buddy | friend |
-----------------------------------
3 |1 | buddy | friend |
-----------------------------------
2 |4 | buddy | friend |
------------------------------------
How would I structure a query to find the number of mutual friends between userid 4
and userid 1
.
Thanks.