SELECT the teams with >1 spanish player

Question

I am aware that this is a very easy question for the StackOverflow community. However I haven't found an answer to my problem on StackOverflow neither on other sites.

Let me explain the issue.

I want a query to select the teams with more than 1 Spanish player on them.

Those are the team (equipos) and player (jugadores) tables: https://i.sstatic.net/QMC0J.jpg

My query:

SELECT
    nombre
FROM
    equipos
WHERE
    id IN (
        SELECT
            id_equipo
        FROM
            jugadores
        WHERE
            upper(procedencia) = 'SPAIN'
    )
GROUP BY nombre
HAVING COUNT(*) > 1;

The query is not returning anything, however when I execute a test query to check if there are teams with more than 1 spanish player I get 1 team with 2 spanish players, so I would be expecting to get that team on my above query.

Test query returning a team with 2 spanish players:

SELECT
    j.nombre,
    e.nombre AS equipo
FROM
    jugadores j
JOIN equipos e ON j.id_equipo = e.id
WHERE
    upper(procedencia) = 'SPAIN';

Returning values:

Jorge Garbajosa Raptors
Jose Calderon   Raptors
Sergio Rodriguez    Trail Blazers
Pau Gasol   Lakers
Juan Carlos Navarro Grizzlies

As you can see the Raptors have 2 spanish players. I must be missing something on my initial query. Im not sure about my HAVING CLAUSE tbh.

Answer 1

You can try the query below with having clause

SELECT
    e.nombre AS equipo
FROM
    jugadores j
JOIN equipos e ON j.id_equipo = e.id
WHERE upper(procedencia) = 'SPAIN'
GROUP BY upper(procedencia), e.nombre
HAVING COUNT(j.nombre)>1;