Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select records only if joined table has a count greater than 10?

Tags:

sql

mysql

I need to select the memberid where count of logins is greater than 10

I wrote this query:

SELECT COUNT(DISTINCT(member.member_id))
FROM member
LEFT JOIN location_member ON (member.member_id = location_member.member_id)
WHERE Month(FROM_UNIXTIME(member.r_date)) = 11
   AND Year(FROM_UNIXTIME(member.r_date)) = 2011
   AND FROM_UNIXTIME(member.r_date, '%d') = 28
   AND member.pc_address_validated <> 1
   **OR (if checkins > 10)**

How can I check if the count is more than 10?

like image 658
ppant Avatar asked Dec 03 '11 06:12

ppant


1 Answers

SELECT memberId
FROM checkin
GROUP BY memberId
HAVING COUNT(1) > 3

To address your new query:

SELECT member.member_id
FROM member
INNER JOIN checkins ON member.member_id = checkins.member_id
LEFT JOIN location_member ON (member.member_id = location_member.member_id)
WHERE Month(FROM_UNIXTIME(member.r_date)) = 11
   AND Year(FROM_UNIXTIME(member.r_date)) = 2011
   AND FROM_UNIXTIME(member.r_date, '%d') = 28
   AND member.pc_address_validated <> 1
GROUP BY member.member_id
HAVING COUNT(1) > 10
like image 86
Adam Wenger Avatar answered Oct 17 '22 14:10

Adam Wenger