trevor case trevor case - 1 month ago 6
MySQL Question

Query num times users attended same party

I have the schemas below

Friends Table
----------
user1_id
user2_id

Party
-----
user_id
party_id


And I'm trying to find the num of parties user1 and user2 both attended. So for example

Friends Table
----------
user1 | user 2
1 2 ( user 1 is connected to user 2)
1 4 ( user 1 is connected to user 4)
2 3
2 4

Party
--------
userid | partyid
1 1 (user 1 joined party 1)
2 1 ( user 2 also joined party 1)
1 2
2 2
1 3
2 3
4 3


Would return results that user 1 and user 2 both attended 3 of the same parties, user 2 and 4 joined 1 party together, and user 1 and 4 also joined 1 party together

I was thinking of grabbing all parties user1 attended, then grab all parties user2 attended and using a UNION on party_id, but is there a way to do that in one query for all users?

Answer

Can you clarify what User connected means?

Ignoring user connected, you could try the following

select P1.userId, P2.userId, count(*)
from Party as P1, Party as P2
where P1.partyId = P2.partyId and P1.userId < P2.userId
group by P1.userId, P2.userId

This would give you the count of the number of parties that each user pair of users went to together.