I have two tables in the database. One with fans and another with teams. In the amateur table there is a field that is the team they follow.
I need a single query that returns all the fields of the equipment table and also tells me the number of followers for each team.
SELECT * FROM equipos
SELECT COUNT(*) FROM aficionados WHERE id_equipo = $id_equipo
This way I would get it in two queries, but how could I do it in one?
Without knowing which fields your team table has (and considering that there may be many fields or that you can add fields in the future) the most flexible way is with a derived table:
SELECT equipos.*,
afc.conteo
FROM equipos
LEFT JOIN (SELECT id_equipo,
count(*) conteo
FROM aficionados
GROUP BY id_equipo) afc ON equipos.id = afc.id_equipo
Now, if the important thing were only a handful of fields (for example, the name and country of the team) it would be more elegant to do
SELECT equipos.id,
equipos.nombre,
equipos.pais,
count(*) as conteo
FROM equipos
LEFT JOIN aficionados ON equipos.id = aficionados.id_equipo
GROUP BY equipos.id, equipos.nombre, equipos.pais
Another way, apart from the amenadiel, would be to put your second query in the select of the first:
SELECT
E.*, (SELECT COUNT(*) FROM Aficionados WHERE id_equipo = $id_equipo) AS TOTAL
FROM
Equipos as E