Group by is not working with where in clause mysql?

Question

Hi I have two tables one is user and another one is user_details

The user table is

id    name  
---------
1     John    
2     Tom         
3     Amy 
4     Kat

The user_details table is

id    user_id   money     created_on
------------------------------------
1       1        2000     2016-12-01
2       1        5000     2016-12-02
3       1        6000     2016-12-03
4       2        2050     2016-12-01
5       2        5020     2016-12-02
6       3        6000     2016-12-04

And I need result like

name   user_id   money     created_on
------------------------------------
John       1        6000     2016-12-03
Tom        2        5020     2016-12-02
Amy        3        6000     2016-12-03
Kat        4        NULL     NULL

My question is I need records where the recent created_on of user_details table, for this I wrote following query but I am getting all the results could any one tell me what wrong in my query

SELECT * FROM `user` as `u` LEFT JOIN ( SELECT   user_id,money,created_on      FROM `user_details` WHERE created_on IN  (SELECT MAX(created_on) FROM    user_details group by user_id )) `userdata` ON `u`.`id` =  `userdata`.`user_id`;

Answer 1

In your inner subquery, you just group by the user_id, but don't filter by the user_id of the user you are currently selecting in your other query.

In other words: You don't even need to group by user_id, just select the MAX(created_on) of the given user by using an alias in the outer subquery like so:

SELECT * FROM `user` as `u` LEFT JOIN 
    ( SELECT   user_id,money,created_on FROM `user_details` x WHERE created_on IN  
        (SELECT MAX(created_on) FROM user_details 
               WHERE x.user_id = user_details.user_id)) 
        `userdata` ON `u`.`id` =  `userdata`.`user_id`;