CODEWITHSUNDEEP
mysqljoin
mgm
mgm

Reputation: 143

Count in multiple joins

I need to get the distinct list of users ids which have made their live check-in (checkins.ctype='live') in a match where are also lived-check-in three (3) or more of his friends.

Here is the DB design :

enter image description here

I successfully retrieved the list of matches where are more then 3 users with live checkin, but I need to find out if they are my friends.

My code untill now :

SELECT DISTINCT
    f1.id
FROM 
    fanusers f1

JOIN    checkins c
ON c.fanuser_id = f1.id

WHERE
c.ctype = 'live'
AND
c.match_id IN (
                                SELECT
                                    c1.match_id
                                FROM
                                    checkins c1
                                WHERE
                                    c1.ctype = 'live'
                                GROUP BY
                                    c1.match_id
                                HAVING
                                    COUNT(*)> 3
                            )

...and he has 3 ore more than 3 friends lived checked-in in the same match (c.match_id)

Any Ideea ? Thanks

Upvotes: 0

Views: 57

Answers (1)

Terje D.
Terje D.

Reputation: 6315

What about joining checkins to fanuser_friends on fanuser_id, then further join to checkins on friend_id, finding the number of relevant friends by grouping by fanuser_id and match_id, and then limiting the result on the number of friends?

I.e.

SELECT DISTINCT c.fanuser_id
FROM checkins c 
INNER JOIN fanuser_friends f on 
f.fanuser_id = c.fanuser_id 
INNER JOIN checkins c2
on c2.fanuser_id = f.friend_id
AND c2.match_id = c.match_id
WHERE c.ctype = 'live'
AND c2.ctype = 'live'
GROUP BY c.fanuser_id, c.match_id
HAVING COUNT(*) >= 3;

Upvotes: 1

Related Questions