Bonjour,

Je suis en train de suivre le cours OCM Administrez vos bases de données avec MySQL

J'ai donc 3 tables dans une base :

- 1 table animal avec la liste des animaux (notamment en colonne : id, race_id et espece_id)

- 1 table espece avec la liste des espèces (id, nom) : chien, chat, tortue, perroquet et rat

- 1 table race avec la liste des races (id, nom, espece_id) : berger allemand, berger suisse et rottweiler qui sont des chiens, singapura, bleu russe, maine coon, nebelung et sphynx qui sont des chats. Je n'ai pas de races pour les tortues, les perroquets et les rats.

La table animal comprend des chiens, des chats, des tortues et des perroquets mais pas de rats. Parmi les chiens et les chats, toutes les espèces sont présentes dans la table animal sauf le sphynx.

Je n'arrive pas à créer une unique requête qui me sortirait la liste de toutes les espèces (colonne 1) et toutes les races (colonne 2) (y compris les espèces et les races non présentes dans la table animal) avec pour chaque le nombre d'animaux (colonne 3) (dont le résultat serait 0 pour le sphynx ou le rat par exemple).

J'ai réussi en faisant un union, mais ça me parait bizarre qu'on ne puisse pas y arriver avec des jointures...

Néanmoins, si je fais un LEFT JOIN ou RIGHT JOIN, je vais forcément perdre les données d'une table ou d'une autre, alors que je les veux TOUTES. 

Y-a-t-il une solution simple ?

Merci pour votre aide,

Marie

Bonjour,

MarieDtp a écrit:

si je fais un LEFT JOIN ou RIGHT JOIN, je vais forcément perdre les données d'une table ou d'une autre

Avec deux conditions dans la jointure externe de la table animal :

SELECT
	E.nom AS espece,
	R.nom AS race,
	IF( A.id IS NULL, 0, COUNT(*) ) AS nb_animaux
FROM
	espece E
		LEFT JOIN race R
			ON E.id = R.espece_id
		LEFT JOIN animal A
			ON E.id = A.espece_id
			OR R.id = A.race_id
GROUP BY
	E.nom,
	R.nom

L'espèce est toujours présente, si il y a des races on les affiches, si il y a des animaux (de la race OU de l'espèce) on les compte ...

-
Edité par Benzouye 1 avril 2020 à 19:49:25

Bonjour,

Merci pour la réponse ! Cela me crée effectivement la table que je recherche.

En revanche, la manière de compter n'est pas correcte. Je ne sais pas comment SQL compte, mais là j'ai 20 sur toutes les lignes de race de chat alors que j'ai 20 chats au total, et pareil pour les chiens...

Savez-vous d'où peut venir le problème ?

Merci,

Marie

Oui, car ceux qui ont une race sont comptés plusieurs fois ... je n'y avais pas pensé ...

Il faut les distinguer dans la jointure, genre :

SELECT
	E.nom AS espece,
	R.nom AS race,
	IF( A.id IS NULL, 0, COUNT(*) ) AS nb_animaux
FROM
	espece E
		LEFT JOIN race R
			ON E.id = R.espece_id
		LEFT JOIN animal A
			ON R.id = A.race_id
			OR (
				E.id = A.espece_id
				AND R.id IS NULL
			)
GROUP BY
	E.nom,
	R.nom

ça compte comme il faut en effet !

Par contre ça n'affiche pas les 8 chiens et 2 chats qui n'ont pas de race... Pourtant les perroquets, tortues et rat sont bien affichés et comptés alors qu'ils n'ont pas de race... Je ne comprends pas comment SQL compte ! Pouvez-vous m'éclairer ?

Il y a le code ici avec les données : https://openclassrooms.com/fr/courses/1959476-administrez-vos-bases-de-donnees-avec-mysql/1970063-transactions

Merci beaucoup !

Marie

C'est à s'arracher les cheveux cette histoire

Il y a un truc tordu en fait ...

En faisant ceci :

SELECT E.nom_courant AS Espèce, COALESCE( R.nom, '' ) AS Race, COUNT(*) AS Nombre
FROM
	Animal A
		LEFT JOIN Espece E
			ON A.espece_id = E.id
		LEFT JOIN Race R
			ON A.race_id = R.id
GROUP BY A.espece_id, A.race_id

On obtiens bien nos 60 animaux avec leur espèce et leur race le cas échéant.

Maintenant il faut y ajouter les races qui n'ont pas d'animaux :

SELECT E.id, R.id, 0
FROM
	Espece E
		INNER JOIN Race R
			ON E.id = R.espece_id
		LEFT JOIN Animal A
			ON R.id = A.race_id
WHERE A.id IS NULL

Il n'y en a qu'une dans le jeu de données, le chat sphynx.

Et enfin y rajouter les espèces sans race et sans animaux :

SELECT E.id, R.id, 0
FROM
	Espece E
		LEFT JOIN Race R
			ON E.id = R.espece_id
		LEFT JOIN Animal A
			ON E.id = A.espece_id
WHERE
	A.id IS NULL
	AND R.id IS NULL

Il n'y en a qu'une dans le jeu de données, le rat brun.

Donc la requête répondant à ta demande serait l'UNION de ces trois requêtes ... l'anti-spam m'empêche de poster la requête avec les UNION ...

Sympa non ? Je ne vois pas comment faire autrement en fait ... Le plus rigoureux selon moi serait de toujours créer une race égale à l'espèce et retirer espece_id de la table animal pour n'y renseigner que race_id ...

-
Edité par Benzouye 3 avril 2020 à 16:36:11

A s'arracher les cheveux oui ! J'en étais arrivée à la même conclusion que toi avant d'ouvrir ce sujet : faire des UNION de requêtes, mais ça me semblait "compliqué" pour un problème simple. Je pensais que c'était mon niveau qui ne me permettait pas d'y arriver, mais ça me rassure de voir que tu en viens à la même conclusion. Merci pour ton aide et pour ton temps !