Search code examples
sqlsubqueryin-subquery

Apply filter on subquery with multiple values

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 )

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