I Have a table USER_DETAILS and I have another table named PASSWORD
USER_DETAILS contains:-
id| name| phone_number
PASSWORD contains:-
id|user_id| password| created_at | password_hint
password can contain multiple entries for a user
Now I want to fetch the latest password created_at for a user for which am doing this.
select user.id,created_at from user_details as user
where user.id in (select user_id,max(created_at) from password group by user_id)
but this will not work because the subquery is returning multiple values how can I do this with subquery(I know I can do this with join as well but I am looking for a subquery solution )
There are numerous ways, with a subquery you would use a correlated query
select id, (select max(created_at) from password p where p.user_id=u.id) as created_at
from user_details u