CODEWITHSUNDEEP
mysql
xivzgrev
xivzgrev

Reputation: 101

Can I perform a SELECT within a WHERE in mysql?

I'm trying to perform a select within a where clause.

Basically, I have a number of users, and trying to see which were active. Active means they logged activity in last 30 days. But, if I join the user table with activity table, then I get duplicate user IDs (because each user may have logged multiple actions).

So I was looking at putting a select inside a where that would check, for each user, that there was at least one action.

SELECT u FROM `users` u
where (
select count(*) FROM `user_activity` ua
where ua.user_id = u.user_id and ua.last_login between "2012-04-01 00:00:00" and "2012-04-30 23:59:59"
) >= 1

Upvotes: 1

Views: 384

Answers (3)

Ami
Ami

Reputation: 1254

Yes, you can put a SELECT in a WHERE clause.

I would avoid the correlated subquery with a JOIN to see if it improved the performance:

SELECT DISTINCT `user`
FROM users u
JOIN user_activity ua
  ON ua.user_id = u.user_id
  AND ua.last_login BETWEEN '2012-04-01 00:00:00' AND '2012-04-30 23:59:59'

Upvotes: 0

zerkms
zerkms

Reputation: 254906

SELECT u
FROM users u
where EXISTS ( select null
               FROM user_activity ua
               where ua.user_id = u.user_id
                 and ua.last_login between "2012-04-01 00:00:00" and "2012-04-30 23:59:59" 
               LIMIT 1)

Thanks to @Ami for pointing about about LIMIT 1 in subquery that potentially could improve performance a bit

Upvotes: 1

apesa
apesa

Reputation: 12443

Yes, you can nest a select inside a where clause like so:

SELECT * FROM mytable WHERE users in(SELECT users FROM  user_activity);

But I don't think you can nest an aggregate function like count(*) inside a where clause. So I would try this first and then the aggregate function, but try to write your select without the aggregate. I don't have you data in front of me so I can't help there.

Upvotes: 0

Related Questions