My goal is to select the list of cats and amazon birds, with their sex, their speecie (latin name) and their race (if it exists) and group the cats together, and the amazong birds together, and inside of their speecie, groupe the race together.
I first failed to have the right query but then I saw the solution and don't understand it very well.
So here's the solution :
SELECT Animal.nom as nom_animal, Animal.sexe, Espece.nom_latin as espece, Race.nom as race
FROM Animal
INNER JOIN Specie
ON Animal.specie_id = Specie.id
LEFT JOIN Race
ON Animal.race_id = Race.id
WHERE Specie.nom_courant IN ('Amazon birds', 'Cat')
ORDER BY Specie.nom_latin, Race.nom;
Why this can't be :
FROM Animal
INNER JOIN Specie
ON Animal.specie_id = Specie.id
INNER JOIN JOIN Race
ON Animal.race_id = Race.id
WHERE Specie.nom_courant IN ('Amazon birds', 'Cat')
GROUP BY Specie.nom_latin, Race.nom;
Because as I understood : I only have to select Animal when it's available
Also what is the practicl use of GROUP BY and ORDER BY and how to know which one to use and when ?
The query has been copied from a french source.
It has to do with the way the database is created and the data is stored.
If all animals had race then it would not matter -- at all. But it seems it's not the case of your example.
It seems that for some animals the Animal.race_id is null. When that happens, do you want to show the animal anyway with an empty race?
LEFT JOIN: shows all the animals, regardless they have or not race.INNER JOIN: shows all the animals with their race; if an animal does not have a race, it's not shown.Second question: ORDER BY vs GROUP BY
These two are quite different: