Select in two tables with Count

Question

I have two tables in Mysql

clientes
id
nome
cat

categorias
id
nome

I need to create a SELECT, that shows all categories, but only categories that have more than 10 clients (for example).

I wanted it in just one SELECT, but I can’t think what it would be like...

I tried that:

SELECT nome
  FROM categorias
 INNER JOIN clientes
    ON clientes.cat = categorias.id
 GROUP BY categorias.nome
HAVING COUNT(clientes.id) >=10 

Answer 1

Your query is almost right, the problem in it is, both tables have a column called "name", and this will give ambiguous reference, you need to prefix which table is using the column:

SELECT categorias.nome 
  FROM categorias 
 INNER JOIN clientes ON clientes.cat = categorias.id 
 GROUP BY categorias.nome 
having count (clientes.id) >=10

You can see working here (I put the quantity 5 to avoid making many Inserts): https://www.db-fiddle.com/f/j8FBajK7tWnaU6oTrKJkV2/0

You can change Count to <= 3 and you’ll find more categories.

Answer 2

You can use a subquery in the WHERE to check how many records you have in the table clientes with the category of the line in question:

SELECT cat.nome
  FROM categorias cat
 WHERE 10 <= (SELECT COUNT(1)
                FROM clientes cli
               WHERE cli.cat = cat.id)