CODEWITHSUNDEEP
mysqlsql
Accountant م
Accountant م

Reputation: 7483

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

Upvotes: 0

Views: 64

Answers (2)

Sanka
Sanka

Reputation: 1324

Just try to catch my approach. I haven't compile query. I need to highlight this one

(select count(*) from log where request.id=log.request)

Full SQL

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

Upvotes: 1

Scarabee
Scarabee

Reputation: 5704

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

Upvotes: 1

Related Questions