CODEWITHSUNDEEP
phpmysqljoin
paultje182
paultje182

Reputation: 159

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.

Upvotes: 2

Views: 99

Answers (1)

TehSphinX
TehSphinX

Reputation: 7430

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 b.date BETWEEN DATE_SUB("2016-07-17", INTERVAL 3 DAY) AND "2016-07-17" AND i.postid = a.postid) 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 :)

Upvotes: 1

Related Questions