I have database with two tables - 'Warehouses' and 'Boxes'. Each box has field with warehouse code, each Warehouse - 'capacity' field.
The purpose is to find only Warehouses that are "overfilled" (capacity of warehouse is less then number of all boxes with this warehouse code).
So, I count all boxes and join warehouse capacity by this query:
SELECT Warehouses.Code, Warehouses.Capacity, COUNT(Boxes.Code)
FROM `Warehouses` RIGHT JOIN
`Boxes`
on Warehouses.Code = Boxes.Warehouse
GROUP BY Boxes.Warehouse
Result:
------------------------------
Code | Capacity | COUNT
------------------------------
1 | 3 | 4
------------------------------
2 | 4 | 2
------------------------------
3 | 7 | 2
------------------------------
4 | 2 | 1
------------------------------
That returns me warehouse's capacity and counts boxes in it, but I don't know how and where to compare these numbers.
You do this in a HAVING clause:
SELECT w.Code, w.Capacity, COUNT(b.Code)
FROM `Warehouses` w LEFT JOIN
`Boxes` b
on w.Code = b.Warehouse
GROUP BY w.Code, w.Capacity
HAVING w.Capacity < COUNT(b.Code);
Notes:
LEFT JOIN is generally much easier to understand than RIGHT JOIN ("Keep all rows in the first table" versus "keep all rows in the last table, which I haven't read yet"). However, this query probably only needs an INNER JOIN.Warehouses should be the first table, because your question is about this entity.HAVING clause does the comparison after the aggregation.Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments