CODEWITHSUNDEEP
mysqljoin
Danny
Danny

Reputation: 1195

MySQL Select only 1 record on a join including no matches

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

Answers (2)

sgeddes
sgeddes

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

James Green
James Green

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

Related Questions