Join Equivalent For User Session Subquery-CodePudding

I have a user table with id column and request table with user_id and session_id columns.

I want to get the number of sessions each user has.

Here is the query I wrote using a subquery:

select user.id, 
(
    select count(distinct session_id)
    from request
    where request.user_id = user.id 
) as session_count
from user

What would be the join equivalent of this?

CodePudding user response：

What about this?

select u.id, count(distinct r.session_id)
from user u
join request r on r.user_id = u.id 
group by u.id

CodePudding user response：

You can use GROUP BY without need a subquery such as

SELECT u.id, COUNT(DISTINCT r.session_id)
  FROM user u
  JOIN request r
    ON u.id = r.user_id
 GROUP BY u.id