MySQL query - select from one, count from another table

Question

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.

First depot looks overloaded (4>3) the others not. What do you need to do more? What is the issue? — user2399035
– user2399035, Commented Jan 26, 2017 at 12:09

Gordon Linoff · Accepted Answer · 2017-01-26 13:22:09Z

2

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.

edited Jan 26, 2017 at 13:22

answered Jan 26, 2017 at 12:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

HAVING w.Capacity > COUNT(b.Code) will get the warehouses where there is still capacity.. topicstarter asked for the "overfilled" warehouses where there to many boxes vs capacity... so it should be HAVING w.Capacity < COUNT(b.Code)

Collectives™ on Stack Overflow

MySQL query - select from one, count from another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related