T
The problem is that some of the values are being counted multiple times. This is because of the fact that COUNT of the id's without checking that those id's have not been counted before. The same mistake will happen with the SUM, so the case could be given that the same vows are added several times.By making a LEFT OUTER JOIN, they keep all the records of the tables on the left and are combined with the records of the table on the right (or with NULL if there is none). The problem is that by keeping the values on the left, some of them are duplicating (or multiplying) because they are "re-added" for each record on the right.To see this better, we're going to remove the COUNT, SUM and GROUP BY of your SELECT, which leaves us the following sentence:SELECT w.id,
d.id AS Downloads,
f.id AS Favorites,
v.type AS Likes,
v.type AS Dislikes
FROM wallpapers AS w
LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id
LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id
LEFT OUTER JOIN votes AS v ON w.id = v.wall_id
WHERE w.id = 2
that when executed with the data provided in the question returns the following:id | Downloads | Favorites | Likes | Dislikes
2 | NULL | 2 | like | like
2 | NULL | 2 | like | like
2 | NULL | 2 | like | like
2 | NULL | 2 | dislike | dislike
2 | NULL | 2 | like | like
As you see, first select w.id which is 2 as indicated in the WHERE, there is no download for what Downloads is NULL, it is a favorite with id 2... and now it is when the problem begins: there are 5 votes, for which each row will have the same left side combined with each of the votes. That would not be a problem but rather because now we find that id 2 has been selected 5 times (one for each vote).A quick solution would be to add a DISTINCT to the COUNT to avoid that problem:SELECT w.id,
COUNT(DISTINCT d.id) AS Downloads,
COUNT(DISTINCT f.id) AS Favorites,
SUM(IF(v.type = 'like',1,0)) AS Likes,
SUM(IF(v.type = 'dislike',1,0)) AS Dislikes
FROM wallpapers AS w
LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id
LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id
LEFT OUTER JOIN votes AS v ON w.id = v.wall_id
WHERE w.id = 2
GROUP BY w.id;
That seems to work out in the naked eye, but it keeps leaving you a problem with the SUM (which cannot be seen first with the data placed in the question). Imagine that instead of having a single favorite for the Wallpaper with id 2 (as there are now), there were two. In that case, all votes will be combined with those two producing 10 rows. And the SUM They'd be adding the same vote twice.To fix this, my recommendation would be that instead of using SUM usea COUNT with DISTINCT like for the other values (it's what you're doing anyway, because you're really simulating a COUNT with a SUM). And separate the "Likes" and "Dislikes" with different JOINs.With the changes I say, the SQL sentence would be like this:SELECT w.id,
COUNT(DISTINCT d.id) AS Downloads,
COUNT(DISTINCT f.id) AS Favorites,
COUNT(DISTINCT v1.id) AS Likes,
COUNT(DISTINCT v2.id) AS Dislikes
FROM wallpapers AS w
LEFT OUTER JOIN downloads AS d ON w.id = d.wall_id
LEFT OUTER JOIN favorites AS f ON w.id = f.wall_id
LEFT OUTER JOIN votes AS v1 ON w.id = v1.wall_id AND v1.type = 'like'
LEFT OUTER JOIN votes AS v2 ON w.id = v2.wall_id AND v2.type = 'dislike'
WHERE w.id = 2
GROUP BY w.id
That it already returns the correct values always because even if there are duplicates, only the different ids are being counted.
