Sql query that selects all inactive users

Question

I have a request for implementation.

I have 2 tables: user and login_logs .

The user table is as follows:

id || first_name || last_name ||             email                   || username || activated || suspended

1 || John || Dan || john @ everything || John1 || 1 || 0 ||

2 || Mike || Hutt || microphone @ everything || mike1 || 1 || 0 ||

and etc.

The login_logs table is as follows:

id   ||     login_datetime     || user_id

1 || 2011-01-27 23:04:59 || 1

2 || 2010-01-27 23:04:59 || 2

and etc.

Thus, the login_logs table keeps a record of when the user is logged on.

Now I want to make a request that selects all inactive users. Inactive users are:

1) users who are not logged in for 90 days

2) users who have never logged in

I executed a request that satisfies the first condition, but is not entirely correct:

 SELECT DISTINCT u.id, u.last_name, u.first_name, u.email,u.username

 FROM users u INNER JOIN login_logs l ON l.user_id = u.id 

 WHERE u.activated = 1 AND u.suspended = 0  AND DATEDIFF(CURDATE(), l.login_datetime) <= 90

 ORDER BY u.last_name, u.first_name, u.id

, , , , , .

, , , ( , ).

Answer 1

select u.* from user u left outer join login l
on u.id = l.user_id
where l.user_id is null
or DATEDIFF(CURDATE(), l.login_datetime) <= 90

Answer 2

, LEFT JOIN.

GROUP BY, MAX .

-, - , .

SELECT u.id, u.last_name, u.first_name, u.email, u.username
FROM users u
LEFT JOIN login_logs l ON l.user_id = u.id
WHERE u.activated = 1 AND u.suspended = 0
GROUP BY u.id, u.last_name, u.first_name, u.email, u.username
HAVING IFNULL(max(l.login_datetime), 0) <= ADDDATE(CURDATE(), interval -90 day)
ORDER BY u.last_name, u.first_name, u.id

IFNULL(max(l.login_datetime), 0) , LEFT JOIN login_logs ( ), 0, , , <= 90 days ago; ().