gistfile1.txt

SELECT
                c.id checkin_id,
                u.id,
                u.nickname,
                u.status_text,
                u.photo,
                u.major_job_category,
                u.minor_job_category,
                l.linkedin_data AS headline,
                p.filename,
                c.lat,
                c.lng,
                UNIX_TIMESTAMP(c.checkout_date) AS checkout,
                c.checkout_date,
                c.checkout_date > NOW() as checked_in,
                v.id AS venue_id,
                (SELECT COUNT(*) FROM " . CHECKINS . "
                    WHERE venue_id = v.id AND userid = c.userid) checkin_count,
                (SELECT SUM(UNIX_TIMESTAMP(checkout_date) - UNIX_TIMESTAMP(checkin_date))
                    FROM " . CHECKINS . "
                    WHERE venue_id = v.id 
                    AND userid = c.userid
                    AND checkout_date > NOW() - INTERVAL 7 DAY) checkin_time
            FROM " . CHECKINS . " c
            INNER JOIN " . USERS . " AS u ON u.id = c.userid
            LEFT JOIN " . PHOTOS . " AS p ON u.photo = p.photo
            LEFT JOIN " . VENUES . " AS v ON v.id = c.venue_id
            LEFT JOIN " . USERS_LINKEDIN . " AS l ON l.linkedin_id = u.linkedin_id AND l.field = 'headline'
            WHERE
                u.`active` = 'Y' AND
                v.id in (" . $venue_IDs_string . ")
                $checkin_interval_sql
            GROUP BY u.id, v.id
            ORDER BY c.checkin_date DESC";