Jointures en pagaille : éviter des produits croisés

Bonjour tout le monde !

Je brasse toujours du SQL, et là je suis sur quelque chose qui me pose une colle et me fait bien comprendre que du SQL, je ne connais que les grandes largeurs.

Soit le MCD ci-dessous.

Je souhaite récupérer tous les mandats d’un titulaire donné pour un partenaire donné, chacun avec son titulaire, ainsi qu’une règle spécifique (mais les valeurs titulaire, partenaire et défaut) et une configuration pour le partenaire, sachant que les règles titulaire et partenaire comme les configurations peuvent ne pas exister (mais on a toujours l’enregistrement dans la table * disponible). J’espèrerais n’avoir qu’un résultat par mandat.

J’ai pour l’instant les requêtes ci-dessous (j’ai volontairement remplacé la liste des champs dont j’aurais besoin pour alléger).

SELECT
		*
	FROM
				Mandat md
		INNER JOIN
				Partenaire pn
			ON
					md.pn_id = pn.id
		LEFT JOIN
				Configuration co
			ON
					co.relation_id = pn.id -- relation_id est l'ID d'enregistrement étranger
		LEFT JOIN
				Configuration disponible cd
			ON
					co.cd_id = cd.id
				AND	cd.name = '…'
		INNER JOIN
				Titulaire ti
			ON
					md.ti_id = ti.id
		LEFT JOIN
				Règle de titulaire rt
			ON
					md.id = rt.md_id
		LEFT JOIN
				Règle de partenaire rp
			ON
					rt.rp_id = rp.id
		LEFT JOIN
				Règle disponible rd
			ON
					rp.rd_id = rd.id
				AND	rd.name = '…'
	WHERE
			(md.pn_id = :partenaireId
			OR	rp.pn_id = :partenaireId)
		AND	ti.id = :titulaireId

SELECT
		*
	FROM
				Mandat md
		INNER JOIN
				Partenaire pn
			ON
					md.pn_id = pn.id
		LEFT JOIN
				(SELECT
						co.valeur,
						cd.relation_id
					FROM
								Configuration co
						INNER JOIN
								Configuration disponible cd
							ON
									co.cd_id = cd.id
								AND	cd.name = '…'
				) ct
			ON
					ct.relation_id = pn.id
		INNER JOIN
				Titulaire ti
			ON
					md.ti_id = ti.id
		LEFT JOIN
				(SELECT
						*
					FROM
							Règle de titulaire rt
					INNER JOIN
							Règle de partenaire rp
						ON
								rt.rp_id = rp.id
					INNER JOIN
						Règle disponigle rd
					ON
							rp.rd_id = rd.id
						AND	rd.name = '…'
				) rt
			ON
					md.id = rt.md_id
	WHERE
			(md.pn_id = :partenaireId
			OR	rule_merchant_id = :partenaireId)
		AND rt.id = :titulaireId

Le souci avec la première est que j’ai une sorte de produit croisé : je récupère bien la valeur de la règle que je voudrais, mais les enregistrements sont multipliés par la liste de toutes les configurations, alors que j’aurais souhaité n’en avoir qu’une.

Merci d’avance

Si je comprends bien les informations que tu donnes, je pense que ça peut se régler en :

1. Ajoutant une ou plusieurs contraintes sur la configuration à récupérer, ou
2. Ajoutant un distinct à ton select.

Mais pour en dire plus, il faudrait avoir des précisions sur les colonnes qui tu as omis, justement.

D’autre part, tu peux tout à fait faire quelque chose du genre (exemple complètement fictif) pour sélectionner toutes les lignes de A qui n’a pas de B correspondant :

select A.*
from A
left outer join B on B.a_id = a.id
where B.id is null

(A a au moins une colonne id, B a au moins une colonne id et une colonne a_id qui est une référence sur A.id)

PS : Il me semble qu’il y a une autre syntaxe pour faire ce que j’ai écrit, mais je ne l’ai plus sous la main.

Les colonnes sont plus ou moins les suivantes (en reprenant les alias de tables utilisés dans le premier message) :

md.*,
ti.*, /* avec des alias préfixés par ti_ pour les colonnes qui se retrouveraient
       * dans celles des mandats, d'où le "plus ou moins" */
rt.valeur AS rt_valeur,
rp.valeur AS rp_valeur,
rd.nom AS rd_nom,
rd.valeur_defaut AS rd_valeur_defaut,
co.valeur AS co_valeur

J’avais tenté d’ajouter DISTINCT dans la requête, mais je n’ai pas vu de différence (même nombre de résultats retournés), d’où mon idée de creuser plus du côté des expressions de jointure ou de filtration.

Quand je ne cible qu’un seul mandat, il sort bien dans mes résultats, mais autant de fois que j’ai de configurations, et ce même si elles n’ont pas été "instanciées" pour ce que je cherche. Si je duplique l’expression sur le nom de la configuration dans le WHERE, j’exclus les mandats pour lesquels cette configuration n’a pas été "instanciée".

P.S. : je ne sais pas si les cardinalités sont placées de manière compréhensible sur le MCD, j’ai repris la manière de faire de MySQL Workbench quand on sélectionne UML. Pour les relations entre Titulaire, Mandat et Partenaire par exemple :

• un titulaire possède au minimum un mandat, mais un mandat n’est qu’à un seul titulaire ;
• un mandat fait la liaison entre un partenaire et un titulaire ;
• un partenaire peut n’avoir aucun mandat comme il peut en avoir plusieurs, un pour chaque titulaire intéressé.

C’est compliqué pour le lecteur de se plonger dans tout ça.

Je vois des choses sur les alias, et je suis surpris que MySQL accepte ça. Dans la première requête, en ligne 28, tu as ru.rp_id , mais l’alias ru n’existe nulle part.

Dans la 2ème requête, en ligne 10 et 11, tu as ct.text_value et ct.value_type, alors qu’à ce niveau, ct n’existe pas.

Corriger ces 2 points ne va peut-être pas aider, mais ça ne peut pas faire de mal.

Entre la requête 1 et la requête 2, tu as modifié la façon d’accéder aux 2 tables 'Config' et aux 3 tables 'Règles’. Est-ce que ça marche si tu changes uniquement les lignes correspondant aux tables 'Config' par exemple ?

Pourquoi la requête 2 ne te plait-elle pas ?

Pour étudier tout ça, il faudrait avancer étape par étape. Tu gardes uniquement les tables de base :

select 	*
FROM			Mandat md
	INNER JOIN			Partenaire pn
		ON				md.pn_id = pn.id
	LEFT JOIN			Configuration co
		ON				co.relation_id = pn.id 
	LEFT JOIN			Configuration disponible cd
		ON				co.cd_id = cd.id
			AND	cd.name = '…'
	INNER JOIN			Titulaire ti
		ON				md.ti_id = ti.id
WHERE		(md.pn_id = :partenaireId  )
	AND	ti.id = :titulaireId

Si cette requête donne le nombre de lignes voulues, tu ajoutes plus de tables ; sinon, tu cherches pourquoi ça ne va pas.

Distinct ne peut pas convenir ; group by peut solutionner ton problème, mais ce n’est pas sain.

Ici, tu as des filtres name=’…' sur des tables qui sont 'en bout de chaine’. Tes 2 sous-requêtes me paraissent vraiment la bonne solution.

Et je le conçois volontiers. Verrais-tu quelque chose qui pourrait (t’)aider ?

J’ai corrigé ces histoires d’alias, ce sont de mauvaises adaptations pour ce sujet
Pour avoir pris des noms de tables plus parlants et des alias en conséquence, je me suis retrouvé à remplacer ces derniers là où je ne l’aurais pas dû et oublier des endroits où je l’aurais dû.

Désolé, mais je ne comprends pas ce que tu veux dire par « changer les lignes correspondant aux tables Config », je peux te demander d’être plus explicite.

La requête 2 « ne me plaît pas » parce que je suis moins à l’aise à la conception avec les sous-requêtes. D’autre part, je souhaite savoir s’il y a des finesses possibles avec les jointures, et donc quelque chose à apprendre.

Merci, j’aurais dû commencer par là. Je vais prendre le temps de regarder ça demain.

On semble d’accord pour DISTINCT, par contre je ne vois pas vraiment comment un GROUP BY pourrait aider, à moins d’ajouter un tri personnalisé avec ORDER BY FIELD(…), peut-être ?

Je proposais ceci :

SELECT		*
	FROM	Mandat md
		INNER JOIN	Partenaire pn
			ON	md.pn_id = pn.id
		LEFT JOIN	Configuration co
			ON	co.relation_id = pn.id -- relation_id est l'ID d'enregistrement étranger
		LEFT JOIN	Configuration disponible cd
			ON	co.cd_id = cd.id
				AND	cd.name = '…'
		INNER JOIN	Titulaire ti
			ON	md.ti_id = ti.id
		LEFT JOIN	(SELECT	*
					FROM		Règle de titulaire rt
					INNER JOIN	Règle de partenaire rp
						ON	rt.rp_id = rp.id
					INNER JOIN	Règle disponigle rd
					ON		rp.rd_id = rd.id
						AND	rd.name = '…'
				) rt
			ON	md.id = rt.md_id
	WHERE	(md.pn_id = :partenaireId
		OR	rp.pn_id = :partenaireId)
		AND	ti.id = :titulaireId

Le group by te permettrait de dire <group by pn.id, md.id, ti.id> par exemple. Et donc garder une seule ligne pour chaque combinaison de ces 3 éléments.

Les sous-requetes sont souvent indispensables. Pour la lisibilité (et ça a aussi d’autres avantages), ta 2ème requête peut aussi s’écrire comme ceci :

with 
ct as ( 
          SELECT	co.valeur,	cd.relation_id
	  FROM	Configuration co
	  INNER JOIN	Configuration disponible cd
	  ON		co.cd_id = cd.id
		AND	cd.name = '…' 
        ), 
rt as (
          SELECT	*
	  FROM		Règle de titulaire rt
	  INNER JOIN	Règle de partenaire rp
	  ON		rt.rp_id = rp.id
	  INNER JOIN	Règle disponigle rd
	  ON		rp.rd_id = rd.id
		AND	rd.name = '…'
	)
SELECT		*
FROM		Mandat md
INNER JOIN	Partenaire pn
	ON	md.pn_id = pn.id
LEFT JOIN	ct
	ON	ct.relation_id = pn.id
INNER JOIN	Titulaire ti
	ON	md.ti_id = ti.id
LEFT JOIN	rt
	ON	md.id = rt.md_id
WHERE
	(md.pn_id = :partenaireId
	OR	rule_merchant_id = :partenaireId)
AND rt.id = :titulaireId

Bon, après avoir essayé diverses choses, j’en conclus que ce n’est pas possible comme je l’imaginais.

Merci à vous deux pour votre aide