Mysql query using INNER JOIN with HAVING

Question

I have two tables in Mysql.

The first table curriculo has as ID cur_codigo.

The second table envio possesses env_codigo as ID and env_curriculo as a foreign key to cur_codigo.

The table envios refers to the number of submissions made for each of the registered curricula. Therefore, each CV has a different amount of submissions recorded in the table envios.

I’m trying to create a query for display only the total quantity of resumes, whose quantity of shipments is less than 3, however, I could not make it return the expected result.

The detail is that this query is a subquery, that is, it needs to return only one line. And with group by, it’s returning an error, because, as we know, it returns more than one line.

Below is the query:

select 
(select count(cur_codigo) from curriculo CUR INNER JOIN envio ENV ON 
CUR.cur_codigo=ENV.env_curriculo
group by cur_codigo
having count(ENV.env_codigo)<3) as qtdeCurriculo
from curriculo

The output should return -> 5

Follows SQL Fiddle code: http://sqlfiddle.com/#! 9/27f371/6

Answer 1

The clause HAVING is used to restrict the results returned by the clause GROUP BY

Then the course code was added, and the clause GROUP BY:

select cur_codigo, count(cur_codigo) from curriculo CUR INNER JOIN envio ENV ON 
CUR.cur_codigo=ENV.env_curriculo
group by cur_codigo
having count(cur_codigo)<3

Thus returning as expected:

[Upshot]

| cur_codigo | count(cur_codigo) |
|------------|-------------------|
|        300 |                 2 |
|        400 |                 1 |
|        600 |                 1 |
|        700 |                 2 |
|        800 |                 2 |

---

Edit:
We can do an understatement, and only make one count.

SELECT count(1)
  FROM (SELECT count(cur_codigo)
          FROM curriculo CUR
         INNER JOIN envio ENV ON CUR.cur_codigo=ENV.env_curriculo
         GROUP BY cur_codigo
        HAVING count(cur_codigo)<3) qtdCursos