CODEWITHSUNDEEP
mysqlsqljoinsubquery
iamthereplicant
iamthereplicant

Reputation: 232

Unknown Column in 'IN/ALL/ANY' subquery

I've got 2 tables: members and member_logs.

Members can belong to groups, which are in the members table. Given a date range and a group I'm trying to figure out how to get the 10 days with the highest number of successful logins. What I have so far is a massive nest of subquery terror.

SELECT count(member_id) AS `num_users`,
DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
FROM member_logs 
WHERE `login_success` = 1 
and `reg_date` IN 
    (SELECT DISTINCT DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
     FROM member_logs 
     WHERE `login_success` = 1 
     and (DATE_FORMAT(`login_date`,'%Y-%m-%d') BETWEEN '2012-02-25' and '2014-03-04'))
and `member_id` IN 
    (SELECT `member_id` 
     FROM members 
     WHERE `group_id` = 'XXXXXXX' 
     and `deleted` = 0) 
ORDER BY `num_users` desc 
LIMIT 0, 10

As far as I understand what is happening is that the WHERE clause is evaluating before the subqueries generate, and that I also should be using joins. If anyone can help me out or point me in the right direction that would be incredible.

EDIT: Limit was wrong, fixed it

Upvotes: 3

Views: 19559

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 181037

As a slightly more index friendly version of the previous answers;

To make the query index friendly, you shouldn't do per row calculations in the search conditions. This query removes the per row calculation of the string format date in the WHERE, so it should be faster if there are many rows to eliminate by date range;

SELECT COUNT(*) num_users, DATE(login_date) reg_date
FROM member_logs JOIN members ON member_logs.member_id = members.member_id
WHERE login_success = 1 AND group_id = 'XXX' AND deleted = 0 
  AND login_date >= '2012-02-25' 
  AND login_date < DATE_ADD('2014-03-04', INTERVAL 1 DAY)
GROUP BY DATE(login_date)
ORDER BY num_users DESC 
LIMIT 10

Upvotes: 0

Rafa Paez
Rafa Paez

Reputation: 4860

The first subquery is totally unnecessary because you can filter by dates directly in the current table member_logs. I also prefer a JOIN for the second subquery. Then what you are missing is grouping by date (day).

A query like the following one (not tested) will do the job you want:

SELECT COUNT(ml.member_id) AS `num_users`, 
  DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
FROM member_logs ml
INNER JOIN members m ON ml.member_id = m.member_id
WHERE `login_success` = 1 
  AND DATE_FORMAT(`login_date`,'%Y-%m-%d') BETWEEN '2012-02-25' AND '2014-03-04'
  AND `group_id` = 'XXXXXXX' 
  AND `deleted` = 0 
GROUP BY `reg_date`
ORDER BY `num_users` desc 
LIMIT 10

Upvotes: 3

Jayvee
Jayvee

Reputation: 10875

SELECT count(member_id) AS `num_users`,
DATE_FORMAT(`login_date`,'%Y-%m-%d') AS `reg_date` 
FROM member_logs 
WHERE `login_success` = 1 
and `login_date` IN 
    (SELECT `login_date` 
     FROM member_logs 
     WHERE `login_success` = 1 
     and (DATE_FORMAT(`login_date`,'%Y-%m-%d') BETWEEN '2012-02-25' and '2014-03-04'))
and `member_id` IN 
    (SELECT `member_id` 
     FROM members 
     WHERE `group_id` = 'XXXXXXX' 
     and `deleted` = 0) 
Group by `login_date`
ORDER BY `num_users` desc 
LIMIT 0, 10

Upvotes: 0

Related Questions