0

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 )

Newton8989
  • 300
  • 1
  • 4
  • 22

1 Answers1

1

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
Stu
  • 30,392
  • 6
  • 14
  • 33