Problems with a MYSQL query - Categories and Items

Question

I’m doing a project, which basically consists of item and category... or better "animes" and "episodes"...

Here’s the problem, I want to list all the registered anime and a count of the episodes related to them, for example. " Katekyo - 24 episodes"... that I got, but in the list only comes the anime that are related to them, the anime that are not with episodes do not appear in the query!

Currently

Table Animes

id_anime   nome_anime   capa_anime   visualizacoes_anime
1          Anime1       capa.png     250
2          Anime2       capa.png     250
3          Anime2       capa.png     250

Episode Table

id_episodio   numero_episodio   capa_episodio   visualizacoes_episodio   id_anime
1             Episodio1         capa.png        250                      1
2             Episodio2         capa.png        250                      1
3             Episodio2         capa.png        250                      2

Consultation

SELECT nome_anime, animes.id_anime as id_do_anime, COUNT(id_episodio) as quantidade_epis 
FROM animes INNER JOIN episodios_animes ON animes.id_anime = episodios_animes.id_anime 
GROUP BY episodios_animes.id_anime

As a result

nome_anime   id_do_anime   quantidade_epis
Anime1       1             2
Anime2       2             1

Problem is that only those with episodes counting, the anime 3 that has no episode did not come on the list, and I wanted it to come all, and if there is no episode in it, come a zero in the case!

Answer 1

Replace the INNER JOIN of your query by a LEFT JOIN.

When there is no value in the table to the right, it will still bring the result of the table to the left with the null values to the right.

To bring ordered as last inserted episodes, just make a ORDER BY episodios_animes.id_episodio DESC, once the last entered will have the largest id’s.

SELECT
  nome_anime,
  animes.id_anime as id_do_anime,
  COUNT(id_episodio) as quantidade_epis 
FROM animes
LEFT JOIN episodios_animes ON animes.id_anime = episodios_animes.id_anime 
GROUP BY animes.id_anime
ORDER BY episodios_animes.id_episodio DESC

See working on sqlfiddle