Reputation: 1195
I have a query that shows a list of users and their last login date. It works "if" they have login history but it doesn't include them if they have no login history. How can I get all the users to show even if they do not have a login history?
SELECT SQL_CALC_FOUND_ROWS growers.*,ld2.ip,ld2.login_date as last_login
FROM growers
LEFT JOIN (
SELECT *
FROM log_logins
WHERE user_type = "grower"
ORDER BY login_date DESC
)
as ld2 ON (ld2.user_id = growers.id)
GROUP BY user_id
ORDER BY growers.id DESC
I though the "left" join would include them even if the join did not match anything but it doesn't. The other thing I do not understand is when I delete all log records this query always returns 1 user?
Thanks for the help.
Upvotes: 1
Views: 70
Reputation: 62861
Change your GROUP BY
-- you want to be grouping by growers.id
not ld2.user_id
since ld2.user_id could be NULL
.
SELECT SQL_CALC_FOUND_ROWS growers.*,ld2.ip,ld2.login_date as last_login
FROM growers
LEFT JOIN (
SELECT *
FROM log_logins
WHERE user_type = "grower"
ORDER BY login_date DESC
)
as ld2 ON (ld2.user_id = growers.id)
GROUP BY growers.id
ORDER BY growers.id DESC
Upvotes: 1
Reputation: 1763
You've the right idea using LEFT JOIN, but I think you can simplify the subquery a little.
Untested, but try this:
SELECT SQL_CALC_FOUND_ROWS growers.*,ld2.ip, ld2.login_date as last_login
FROM growers
LEFT JOIN log_logins ld2 ON (user_id = growers.id)
WHERE ld2.login_date = ( SELECT MAX(login_date) FROM log_logins WHERE user_id = ld2.user_id)
GROUP BY user_id
ORDER BY growers.id DESC
(If it's immensely slow, indexing log_logins on both login_date and user_id ought to help a great deal with the subquery!)
Upvotes: 0