How to get data from three different tables

Question

This question is continuing of this issue: How to get data from two different tables?

Now I need to connect the table Sessions with 2 tables:

• Input
• Downloads

When I use this query with real data:

SELECT sessions.ip
    , COUNT(sessions.id)
    , COUNT(input.input) as TotalInputs
    , COUNT(DISTINCT input.input) as UniqInputs
    , COUNT(downloads.shasum) as files
    , COUNT(DISTINCT download.shasum) as Uniqfiles 
FROM sessions, input, downloads 
WHERE sessions.id = input.session 
    AND sessions.session = downloads.session 
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01" 
GROUP BY sessions.ip 
ORDER BY COUNT(sessions.id) DESC LIMIT 5;

I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | Uniqfiles
IP1 |              11145    |       11145   | 15            | 11145 |         8
IP2 |               9125    |        9125   | 71            |     0 |         0
IP3 |               7882    |        7882   | 56            |  7882 |        19

But numbers for count(sessions.id), TotalInputs and Files are not accurate. For example if I use this query:

SELECT downloads.shasum 
FROM sessions, downloads 
WHERE sessions.id = downloads.session 
    AND date_format(sessions.starttime, '%Y-%m-%d') > "2015-01-01" 
    AND sessions.ip = "IP3";

I find that Files count for IP3 have proper value of 752 (not 7882). Real value for TotalInputs is less than COUNT(sessions.id).

How can I fix my query?

---

Sample data is available on this SQL Fiddle.

Using the above query and below sample data, I get this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 3                     | 3             | 2             | 3     | 1
IP3 | 8                     | 8             | 4             | 8     | 2

I need this output:

ip  | COUNT(sessions.id)    | TotalInputs   | UniqInputs    | files | uniq_files
IP2 | 1                     | 3             | 2             | 1     | 1
IP3 | 3                     | 5             | 4             | 3     | 2

How can I update my query ?

---

Sample Sessions data:

id      | starttime             | endtime               | sensor    | ip    | termsize  | client 
id1     | 2015-05-07 11:01:20   | 2015-05-07 18:01:32   | 10        | IP3   | 80x50     | 3
id2     | 2015-05-07 18:03:20   | 2015-03-07 18:11:32   | 2         | IP2   | 80x50     | 1
id3     | 2015-05-07 23:05:20   | 2015-06-07 18:10:32   | 10        | IP3   | 80x70     | 3
id4     | 2015-05-07 13:05:20   | 2015-05-09 20:05:32   | 7         | IP3   | 60x30     | 5

Sample Input data:

id  | session   | timestamp             | realm | success   | input
1   | id1       | 2015-07-13 10:29:18   | NULL  | 1         | date
2   | id3       | 2015-08-13 10:11:18   | NULL  | 0         | aaa
3   | id1       | 2015-03-13 10:11:18   | NULL  | 0         | aaa
4   | id1       | 2015-07-14 10:33:15   | NULL  | 1         | uname
5   | id3       | 2015-05-19 20:33:11   | NULL  | 1         | netstat
6   | id2       | 2015-09-22 10:53:21   | NULL  | 1         | pwd
7   | id2       | 2015-09-22 10:58:11   | NULL  | 1         | pwd
8   | id2       | 2015-11-03 09:53:07   | NULL  | 0         | bbb

Sample Downloads data:

id  | session   | timestamp             | url           | outfile   | shasum
1   | id1       | 2014-07-13 12:15:47   | http://xxx    | xxx       | SHA1
2   | id2       | 2014-09-13 12:18:50   | http://xxx2   | xxx2      | SHA2
3   | id1       | 2015-09-11 13:20:50   | http://xxx3   | xxx3      | SHA1
4   | id3       | 2016-01-19 18:21:30   | http://xxx4   | xxx4      | SHA3

Answer 1

This query:

• Uses ANSI JOIN (LEFT, INNER, ...)
• Uses LEFT JOIN for each table in order to count Sessions IPs without Input or Downloads
• Uses DISTINCT for each COUNT in order to remove duplicates added by the JOIN between tables
• Counts values for totals and counts ids for unique counts

Query:

SELECT s.ip
    , COUNT(DISTINCT s.id)
    , COUNT(DISTINCT i.id) as TotalInputs
    , COUNT(DISTINCT i.input) as UniqInputs
    , COUNT(DISTINCT d.id) as files
    , COUNT(DISTINCT d.shasum) as Uniqfiles 
FROM sessions s
LEFT JOIN input i
    ON s.id = i.session
LEFT JOIN downloads d
    ON s.id = d.session 
GROUP BY s.ip;

SQL Fiddle

Output:

ip  | COUNT(DISTINCT s.id)  | TotalInputs   | UniqInputs    | files | Uniqfiles
IP2 |       1               |       3       |   2           | 1     |   1
IP3 |       3               |       5       |   4           | 3     |   2

Answer 2

@JulienVavasseur is correct in that you need to format your queries using Ansi92 join syntax. If nothing else, it will make it easier for us to read and help you with your questions. However, looking at the problem, it seems as easy as adding a Distinct to the count of session.id to reduce it from 7882 to 752. When you ask for a count, you're going to get a count of the rows, not a distinct count of the sessionid (unless you ask for distinct).