I have 3 tables: residual_types, containers, collections and collection_container. Each container has a residual_type and there is many-to-many relationship between containers and collections.
I need to make query that, in a given day, tells me how much mass has been collected for each residual_type, even though there is not any record associated with the residual_type. For example, in a given day, the "ORGANIC" residual_types has 850 kg collected, it shows "ORGANIC | 850", but if it had 0 kg collected, it would show "ORGANIC | 0".
This is the query I am using, but it seems that it does not respect the WHERE clause for collections.creation_time and it brings all the records
SELECT residual_types.name AS name, IFNULL(SUM(collection_container.mass),0) AS mass
FROM residual_types
INNER JOIN containers ON containers.residual_type_id = residual_types.id
INNER JOIN collection_container ON collection_container.container_id = containers.id
LEFT JOIN collections ON collection_container.collection_id = collections.id AND collections.creation_time BETWEEN 1557637200 AND 1557723599
GROUP BY residual_types.id
ORDER BY mass DESC
+---------+------+
| name | mass |
+---------+------+
| organic | 7580 |
+---------+------+
| paper | 1243 |
+---------+------+
| plastic | 123 |
+---------+------+
I've also tried this query, but it does not bring any records.
SELECT residual_types.name AS name, IFNULL(SUM(collection_container.mass),0) AS mass
FROM residual_types
INNER JOIN containers ON containers.residual_type_id = residual_types.id
INNER JOIN collection_container ON collection_container.container_id = containers.id
INNER JOIN collections ON collection_container.collection_id = collections.id
WHERE collections.creation_time BETWEEN 1557637200 AND 1557723599
GROUP BY residual_types.id
ORDER BY mass DESC
If there are not any collections associated with the residual_type, then the result set should look like this:
+---------+------+
| name | mass |
+---------+------+
| organic | 0 |
+---------+------+
| paper | 0 |
+---------+------+
| plastic | 0 |
+---------+------+
Your problem is that the value you are summing will always be a number, regardless of whether there was a collection or not. You need to condition the sum with whether there was a collection or not, which you can do by changing that expression from
IFNULL(SUM(collection_container.mass), 0)
to
SUM(CASE WHEN collections.id IS NOT NULL THEN collection_container.mass ELSE 0 END)