fritzmg fritzmg - 28 days ago 8
MySQL Question

Fetch records of parent table depending on count of children

I have two tables,

A
and
B
, where
B
has foreign keys to
A
(i.e.
A
can have zero or many children in
B
whereas each
B
record exactly belongs to one
A
record).

Now I want to fetch all records from
A
where the number of children in
B
is lower than x (including zero).

How can I achieve this comparison of the aggregate function?

SELECT A.*, COUNT(B.id) AS child_cnt
FROM A
LEFT JOIN B
ON A.id = B.foreign_id
GROUP BY A.id


However, I cannot add a
WHERE
condition for
child_cnt
of course. Any pointers to how the desired result can be achieved?

Answer

You can use having for filter the result

SELECT A.*, ifnull(COUNT(B.id), 0) AS child_cnt
FROM A
LEFT JOIN B ON A.id = B.foreign_id
GROUP BY A.id
HAVING COUNT(B.id) < X