复杂的MySQL计数使用连接

Question

Basically, what's happening is there is a table which tracks checkins to businesses(BID) by username(UID). $startday = beginning of the day

Checkins:
 ID | UID | BID | DATE

Then there is a second table which tracks friends. User can be a SENDER or RECIEVER

Friends:
 SENDER | RECIEVER | STATUS

What I'm trying to do is count the number of checkins from any friends. So (pseudocode)

(if(SENDER = UID) AND (RECEIVER exists in checkins) AND (DATE > $startday)) OR
(if(RECEIVER = UID) AND (SENDER exists in checkins) AND (DATE > $startday))

Then count those results. I know I'll need to do a JOIN of some kind, but I really don't know where to start with this design. I'd appreciate any help...

Answer 1

try this

SELECT COUNT(1) FROM Checkins ch
JOIN Friends fr
ON (fr.SENDER = ch.UID OR fr.RECIEVER = ch.UID)
WHERE ch.DATE = '$startday'