I have 4 tables and Wallpapers, Downloads, Favorites, Votes which are defined like this. Of wallpapers I only need the id.
Download Table
+----+---------+---------+
| id | wall_id | user_id |
+----+---------+---------+
| 1 | 1 | 1 |
| 2 | 35 | NULL |
| 3 | 35 | NULL |
+----+---------+---------+
Favorite Tables
+----+---------+---------+
| id | user_id | wall_id |
+----+---------+---------+
| 1 | 12 | 10 |
| 2 | 12 | 2 |
+----+---------+---------+
Vote Table
+----+---------+---------+---------+
| id | user_id | wall_id | type |
+----+---------+---------+---------+
| 1 | 12 | 1 | dislike |
| 2 | 12 | 39 | like |
| 3 | 1 | 2 | like |
| 4 | 2 | 2 | like |
| 5 | 3 | 2 | like |
| 6 | 5 | 2 | dislike |
| 7 | 12 | 10 | like |
| 8 | 12 | 2 | like |
+----+---------+---------+---------+
Basically what I need is a query that returns the number of Downloads, Favorites, Likes and Dislikes of a wallpaper. Try nesting with multiple LEFT OUTER JOINs like this:
SELECT w.id,COUNT(d.id) AS Downloads,
COUNT(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 returns the result in the way I need but with the wrong calculations, that is, values are duplicated and other strange things happen.
Results Table
+----+-----------+-----------+-------+----------+
| id | Downloads | Favorites | Likes | Dislikes |
+----+-----------+-----------+-------+----------+
| 2 | 10 | 10 | 8 | 2 |
+----+-----------+-----------+-------+----------+
I understand that it is because the LEFT OUTER JOIN cannot be nested like this since the 3 tables must have records that correspond to the id of the first table.
Any solution?
The problem is that some of the values are being counted multiple times. This is because the id's are being done
COUNTwithout checking that those id's have not been counted before. The same error will occur with theSUM, so it could be the case that the same votes are added several times.Doing a
LEFT OUTER JOINkeeps all the records from the tables on the left and is combined with the records from the tables on the right (or NULL if there are none). The problem is that by keeping the values on the left, you are doubling (or multiplying) some of them because they are "added back" for each record on the right.To see this better we are going to remove the
COUNT,SUMandGROUP BYfrom your SELECT, which leaves us with the following statement:which when executed with the data provided in the question returns the following:
As you can see, first it is selected
w.idthat it is 2 as indicated in theWHERE, there is no download so Downloads is NULL, a favorite with the id 2 is found... and now is when the problem starts: 5 votes are found , whereby each row will have the same left side combined with each of the votes. That wouldn't be a problem if it weren't for the fact that now we find that id 2 has been selected 5 times (once for each vote).A quick fix would be to add a
DISTINCTtoCOUNTto avoid that problem:That seems to work at a glance, but it still leaves you with a problem with the
SUM(which you can't see at first with the data put in the question). Imagine that instead of having a single favorite for the Wallpaper with id 2 (as there is now), there were two. In that case, all the votes will be combined with those two producing 10 rows. AndSUMthey would be adding the same vote twice.To fix this, my recommendation would be to instead
SUMuseCOUNTwithDISTINCTas for the other values (that's what you're doing anyway, because you're really simulating aCOUNTwith aSUM). And that you separate the "Likes" and "Dislikes" with different JOINs.With the changes I say, the SQL statement would be like this:
That already returns the correct values always because even if there are duplicate id's, only the different id's are being counted.
I have given you a fairly long answer based on trying to adjust it as much as possible to the code you presented in the question. Now I am going to give you a
SELECTsimpler one that is valid and with which the same result is obtained, but even so I would recommend the other answer more .This is the query:
As you can see, the query itself is shorter and simpler... but it contains dependent subqueries (something you can see if you do a
EXPLAIN), which will end up slowing it down because they depend on one of the values (w.id) from the main query and must be executed with each row of it.There is an additional third answer to the two that Álvaro provided which is to use derived tables: