Problems with a MYSQL query - Categories and Items

Question

Problems with a MYSQL query - Categories and Items

Navigation

#1 by (5 votes)

8

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

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

Currently

Animes Table

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

Episodes 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

Inquiry

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 comes

nome_anime   id_do_anime   quantidade_epis
Anime1       1             2
Anime2       2             1

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

php mysql query

asked by anonymous 27.02.2015 / 17:48

1 answer

How to determine the last element of a list in a foreach? How to avoid the use of setters in such cases?

score 5 · Accepted Answer

Replace INNER JOIN of your query with LEFT JOIN .

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

In order to bring sorted according to the last inserted episodes, just make a ORDER BY episodios_animes.id_episodio DESC , since the last inserted ones have the biggest 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 in sqlfiddle