Question 20372 in Mysql Question posted in
The official MySQL documentation can be found here.

Correlated sub selects with COUNT condition from another table – Mysql

I am having a problem with figuring out a MySQL query. The pseudo-code would be like this:
table 1 is users table
table 2 is a posts table, and I need to select only those users, who had minimum 5 posts within the time of 1000-2000 seconds of activity.

SELECT * FROM table1 WHERE **( COUNT(id) FROM table2 WHERE `user`=user_id.table1 AND `time`> 1000 AND `time`< 2000 ) > 5** AND active.table1 = 1 LIMIT 0,10
Tags:

2

Answers


  1. 0

    Every subquery needs it own SELECT

    SELECT * FROM table1 
WHERE (SELECT  COUNT(id) FROM table2 WHERE `user`=table1.user_id AND `time`> 1000 AND `time`< 2000 ) > 5 
AND table1.active = 1 LIMIT 0,10
    Login or Signup to reply.
  2. 0

    Use this mysql query:

    SELECT table1.* FROM table1 WHERE table1.active = 1 AND (  SELECT COUNT(*) FROM table2 WHERE table2.user = table1.user_id   AND table2.time >= 1000 AND table2.time <= 2000) >= 5;

    This query returns all rows from table1 where the active column is 1 and the user has posted within the specified time frame. A subquery within the WHERE clause counts the number of posts made by each user. If the number of posts for a given user is greater than or equal to 5, the outer query returns that user’s row from table1.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search