Paultje182 Paultje182 - 4 months ago 7
SQL Question

MySQL Join returns more than expected

This is a problem that keep me 2 days of sleep.

I have 2 tables

views

id | postid | date | count
=================================
13 | 8 | 2016-07-16 | 38
16 | 8 | 2016-07-17 | 35
15 | 9 | 2016-07-16 | 7
17 | 9 | 2016-07-17 | 32
14 | 12 | 2016-07-16 | 17
18 | 12 | 2016-07-17 | 13


visitors

id | postid | date | ip
=================================
13 | 8 | 2016-07-16 | 127.0.0.1
17 | 8 | 2016-07-17 | 127.0.0.1
18 | 8 | 2016-07-17 | 127.0.0.1
16 | 9 | 2016-07-16 | 127.0.0.1
19 | 9 | 2016-07-17 | 127.0.0.1
14 | 12 | 2016-07-16 | 127.0.0.1
15 | 12 | 2016-07-16 | 127.0.0.1
20 | 12 | 2016-07-17 | 127.0.0.1
21 | 12 | 2016-07-17 | 127.0.0.1


And the following query

$query = $wpdb->get_results("
SELECT
SUM(a.count) AS countviews,
COUNT(b.ip) AS countvisitors,
a.postid
FROM views a
RIGHT JOIN visitors b
ON a.postid=b.postid
AND a.date=b.date
WHERE
a.date
BETWEEN
DATE_SUB('2016-07-17', INTERVAL 3 DAY)
AND
'2016-07-17'
GROUP BY
a.postid
ORDER BY
countviews DESC
");


When i print_r the output i'll see the following result

Array
(
[0] => stdClass Object
(
[countviews] => 108
[countvisitors] => 3
[postid] => 8
)

[1] => stdClass Object
(
[countviews] => 60
[countvisitors] => 4
[postid] => 12
)

[2] => stdClass Object
(
[countviews] => 39
[countvisitors] => 2
[postid] => 9
)

)


Only the [countviews] result is higher then expacted. I 'm going to count and see that the countviews from postid 8 must not be '108' but '73'. The stranger thing about it is that the last count of postid 8 is '35'. '108' minus '35' = '73'. So the views tables are count double?

RIGHT JOIN, LEFT JOIN and INNER JOIN gives all the same result.

Answer

You cannot make a join here if you want to count. The relation you made is creating multiples of the view table in case there are multiple days in your search parameters for the same postid.

You can avoid that by using subqueries:

SELECT
    SUM(a.count) AS countviews,
    (SELECT COUNT(b.ip) FROM visitors i WHERE i.postid = a.postid
        AND i.date=a.date) AS countvisitors,
    a.postid
FROM views a
WHERE 
    a.date
BETWEEN 
    DATE_SUB('2016-07-17', INTERVAL 3 DAY) 
AND 
    '2016-07-17' 
GROUP BY 
    a.postid
ORDER BY 
    countviews DESC

Hope I got it right. Let me know if this helps :)