the accountant the accountant - 2 months ago 5
SQL Question

SQL - how to join 3 tables and select the count of many columns

I'm stuck in this situation and don't know how to write the select query that results to what I want. here is the situation

I have 3 tables session, request, log

session table

# session table to store sessions data
ID user
------------
1 John
2 Sarah


request table

# request table to store `http` requests come to me
ID session
------------
1 1
2 1
3 1
4 1
5 2
6 NULL


log table

# log table to store the logs that I record in my php code and store_
# them in the database while processing the requests
ID request
------------
1 1
2 1
3 2





What I want is to select each session and the number of requests made in this session and also it's number of logs. I want the result to be

#sessions result
+----+-------+----------+------+
| ID | USER | requests | logs |
+----+-------+----------+------+
| 1 | John | 4 | 3 |
+----+-------+----------+------+
| 2 | Sarah | 1 | NULL |
+----+-------+----------+------+





What I do is I use joins and group by session Like

select session.*,count(request.id) as requests,count(log.id) as logs
from session left join request on session.id=request.session
left join log on request.id=log.request group by session.id


the problem is when I do this , if 1 request has more than 1 log, it will be duplicated by the join like request 1 in our situation which has logs 1 and 2

# wrong result !! not what I want. notice the count of requests in session 1
+----+-------+----------+------+
| ID | USER | requests | logs |
+----+-------+----------+------+
| 1 | John | ((5)) | 3 |
+----+-------+----------+------+
| 2 | Sarah | 1 | NULL |
+----+-------+----------+------+


What am I doing wrong here ?

thanks

Answer
select session.*,count(DISTINCT request.id) as requests,count(log.id) as logs
from session left join request on session.id=request.session
left join log on request.id=log.request group by session.id
Comments