SQL: Exclusion de résultats avec enregistrement en jointuresSQL: Exclude results with join records

Selon le cas de figure suivant: Nous avons des éléments lié a des couleurs.

Table element

id # name #
1     Sea
2     tree

Table colour

id # name #
1     green
2     blue
3     brown

Table relation

element_id # colour_id
1                 2
2                 1
2                 3

Pour répondre a la demande:

Obtenir les éléments ayant une ou plus des couleurs suivantes

Il nous suffit d’effectuer cette requête (pour les couleurs green et brown):

SELECT element.name, colour.name FROM element
LEFT JOIN relation
ON (element.id = relation.element_id)
LEFT JOIN colour
ON (colour.id = relation.colour_id)
WHERE (relation.colour_id = 1 OR relation.colour_id = 3)

Mais si nous souhaitons répondre a la demande:

Obtenir les éléments ayant toutes les couleurs suivantes

Nous allons devoir utiliser une méthode différente. Ici nous allons utiliser une première requête en utilisant un having count pour isoler les element.id qui ont le nombre de relations (vers les couleurs) suffisantes. Donc pour les couleurs green et brown:

SELECT relation.element_id FROM relation
WHERE relation.colour_id IN (1, 3)
GROUP BY relation.element_id
HAVING COUNT (DISTINCT relation.colour_id) = 2

Nous obtiendrons les element_id qui ont bien deux relations vers couleur, sachant que nous avons filtrer ces relation pour n’être que celle vers le green et le brown. Il n’y a ensuite plus qu’a récupérer nos éléments:

SELECT element.name, colour.name FROM element
WHERE element.id IN (#Ids récupérés#)

In the next case: Elements have colours.

Table element

id # name #
1     Sea
2     tree

Table colour

id # name #
1     green
2     blue
3     brown

Table relation

element_id # colour_id
1                 2
2                 1
2                 3

If we want:

Get elements who have one or more folowing colors

We just have to write this query (for green and brown):

SELECT element.name, colour.name FROM element
LEFT JOIN relation
ON (element.id = relation.element_id)
LEFT JOIN colour
ON (colour.id = relation.colour_id)
WHERE (relation.colour_id = 1 OR relation.colour_id = 3)

But if we need:

Get elements with all following colors

We have to use différent strategy. We use an first query with an having count. So with green and brown:

SELECT relation.element_id FROM relation
WHERE relation.colour_id IN (1, 3)
GROUP BY relation.element_id
HAVING COUNT (DISTINCT relation.colour_id) = 2

We will get element_id who have two relation with colours. Knowing that we have filter these relation in a way to exclude other colors. After we just have to get our elements:

SELECT element.name, colour.name FROM element
WHERE element.id IN (#Ids#)

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *