CODEWITHSUNDEEP
mysqlsql
fritzmg
fritzmg

Reputation: 2605

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?

Upvotes: 0

Views: 23

Answers (2)

Drew
Drew

Reputation: 24959

Use HAVING such as

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

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

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

Upvotes: 1

Related Questions