Hi all
The code below produces the result I need based on the venue
position, though things aren’t playing so kindly with the COUNTs. Not sure if I’m building this correctly
For some reason the reviewCount
and hitsCount
are slightly inflated, meaning if the number should be 12, it returns 96. Both COUNTs also have the exact same number.
SELECT *
FROM ( SELECT v.id
, v.venue_id AS Venue
, COUNT(r.venue_fk) AS reviewCount
, COUNT(h.venue_id) AS hitsCount
FROM tbl_venues v
INNER JOIN tbl_venue_page_hits h
ON (h.venue_id = v.id)
INNER JOIN tbl_reviews r
ON (v.id = r.venue_fk)
WHERE h.dtstamp > CURDATE()
GROUP BY v.venue_id
) AS d
ORDER BY hitsCount DESC
The result based on the code above:
id Venue reviewCount hitsCount
46 v1 609 609
16 v5 208 208
34 v7 184 184
17 v9 136 136
It should be looking something like:
id Venue reviewCount hitsCount
46 v1 29 21
16 v5 22 18
34 v7 34 16
17 v9 28 11
If I remove the reviews table count and join (snippet below), reducing it to two tables, everything works and displays the hits count correctly.
COUNT(r.venue_fk) AS reviewCount
INNER JOIN tbl_reviews r ON (v.id = r.venue_fk)
Two tables without reviews show
id Venue hitsCount
46 v1 21
16 v5 18
34 v7 16
17 v9 11
Any ideas what I’m doing wrong — why the reviews table is causing the incorrect result?
I have left the outer SELECT in place, was playing around with this though the results are the same.
Cheers, Barry