trevor case trevor case - 1 year ago 51
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 Source

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.