CODEWITHSUNDEEP
mysql
Andrew K.
Andrew K.

Reputation: 15

MySQL query - select from one, count from another table

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.

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.
  • Presumably, Warehouses should be the first table, because your question is about this entity.
  • The HAVING clause does the comparison after the aggregation.

Upvotes: 2

Related Questions