Problème d'une vue en SQL [Résolu]

[Marty000]

Bonjour,
je suis sous SQL Server 2005, je dois réaliser une vue SQL et j'avoue que j'ai énormément de problèmes pour la faire.
J'ai trois tables :
- table Carte avec une pk carte_ID
- table Limitation qui est liée à une seule carte et qui a au max quatre règles, pk limitation_ID avec le carte_ID de renseigné et le regle_ID aussi et un champ limitation_valeur
- table Règle qui est liée à une ou plusieurs limitations, pk regle_ID

Ce que je veux c'est récupérer pour une carte toutes les valeurs des limitations, même si elle n'a pas de règles associées. Plus facile à dire qu'à faire (pour moi !).
Le soucis se situe à un niveau particuliers, on définit 4 règles (R1, R2, R3, R4) :
pour une carte qui n'utilise que 3 règles (R1, R2, R3) sur les 4, je veux récupérer les 3 limitations_valeurs, mais aussi NULL pour la règle (R4) qui n'est pas utilisée par la carte, donc en base il n'y a pas de limitation avec regle_ID R4 et la carte...
J'ai beau essayé de faire des jointures externes, je n'y arrive pas.
Le résultat que j'aimerais avoir est le suivant :

carte_ID | limitation_ID | limitation_valeur | regle_ID
1 | 1 | 0.5 | 1
1 | 2 | 0.75 | 2
1 | 3 | 1 | 3
1 | NULL | NULL | 4

Et ce, pour l'ensemble des cartes.
Je ne sais pas si je suis très clair...
En tout cas merci à ceux qui pourront m'aider.

[CinePhil]

Tu peux nous montrer la requête que tu as essayée ?

[Marty000]

Merci de regarder mon problème, voici la requête en cours, mais je ne suis carrément pas fier...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SELECT Carte.Carte_ID AS ID, 
            (case 
                  when TEST.test_sanction=1 then 'test refusé'
                  else(
                  case
                        when Validite.Validite_ID=1 then T1.Regle_libelle+' '+cast(T1.Limitation_valeur AS varchar)+
                             ' '+T3.Regle_libelle+' '+cast(T3.Limitation_valeur AS varchar)
                        else 'carte '+Validite.Validite_libelle 
                        end)
                  end) AS adresse2 ,
            (case
                  when test_sanction=1 then ''
                  else(case
                        when Validite.Validite_ID=1 then T2.Regle_libelle+' '+cast(T2.Limitation_valeur AS varchar)+
                             ' '+T4.Regle_libelle+' '+cast(T4.Limitation_valeur AS varchar)
                        else ''
                        end)
                  end) AS adresse3,
FROM Carte  LEFT OUTER JOIN TEST ON TEST.TEST_ID=Carte.TEST_ID
                  LEFT OUTER JOIN Validite ON Carte.Validite_ID=Validite.Validite_ID
 
                  LEFT OUTER JOIN (SELECT L1.carte_ID,L1.limitation_ID,L1.limitation_valeur,L1.limitation_creeLe,L1.limitation_modifieLe,
                                                     R1.regle_ID,R1.regle_libelle,R1.regle_creeLe,R1.regle_modifieLe
                                         FROM Limitation AS L1 INNER JOIN Regle AS R1 ON L1.Regle_ID=R1.Regle_ID) T1 ON Carte.Carte_ID=T1.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L2.carte_ID,L2.limitation_ID,L2.limitation_valeur,L2.limitation_creeLe,L2.limitation_modifieLe,
                                                     R2.regle_ID,R2.regle_libelle,R2.regle_creeLe,R2.regle_modifieLe
                                         FROM Limitation AS L2 INNER JOIN Regle AS R2 ON L2.Regle_ID=R2.Regle_ID) T2 ON Carte.Carte_ID=T2.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L3.carte_ID,L3.limitation_ID,L3.limitation_valeur,L3.limitation_creeLe,L3.limitation_modifieLe,
                                                     R3.regle_ID,R3.regle_libelle,R3.regle_creeLe,R3.regle_modifieLe
                                         FROM Limitation AS L3 INNER JOIN Regle AS R3 ON L3.Regle_ID=R3.Regle_ID) T3 ON Carte.Carte_ID=T3.Carte_ID
 
                  LEFT OUTER JOIN (SELECT L4.carte_ID,L4.limitation_ID,L4.limitation_valeur,L4.limitation_creeLe,L4.limitation_modifieLe,
                                                     R4.regle_ID,R4.regle_libelle,R4.regle_creeLe,R4.regle_modifieLe
                                         FROM Limitation AS L4 INNER JOIN Regle AS R4 ON L4.Regle_ID=R4.Regle_ID) T4 ON Carte.Carte_ID=T4.Carte_ID
 
WHERE T2.Limitation_ID<>T1.Limitation_ID AND T1.Regle_ID<T2.Regle_ID AND 
      T3.Limitation_ID<>T1.Limitation_ID AND T3.Limitation_ID<>T2.Limitation_ID AND
      T2.Regle_ID<T3.Regle_ID AND T1.Regle_ID<T3.Regle_ID AND 
 
      T4.Limitation_ID<>T1.Limitation_ID AND T4.Limitation_ID<>T2.Limitation_ID AND
      T4.Limitation_ID<>T3.Limitation_ID AND T1.Regle_ID<T4.Regle_ID AND
      T2.Regle_ID<T4.Regle_ID AND T3.Regle_ID<T4.Regle_ID

Sachant que la table Validite permet de savoir si une carte est valide ou pas, et une carte est lié à un objet TEST, je n'avais pas parlé de ces tables avant, car il me semble qu'elles ne sont pas utiles.
Avec cette requête dans le deuxième "case" au niveau de T4.regle_libelle et T4.limitation_valeur je n'ai pas les valeurs de ma 4e règle, mais les valeurs de T3.

[CinePhil]

récupérer pour une carte toutes les valeurs des limitations, même si elle n'a pas de règles associées.

Dans ton exemple de données, tu ne donne que la carte 1 donc je suppose que tu ne veux le résultat effectivement que pour une carte.

Code :

1
2
3
4
SELECT r.regle_ID, l.limitation_ID, l.limitation_valeur
FROM Regle r
LEFT OUTER JOIN Limitation l ON l.regle_ID = r.regle_ID
WHERE l.carte_ID = 1

[Marty000]

Merci de ta solution,
mais ce n'est pas exactement cela que je veux, en effet avec la requête que tu as mis je récupère bien toutes les données, mais il me manque ma ligne pour ma règle 4.

Voici ce que j'ai pour l'ensemble de mes cartes avec ta requête :
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 | 1 | 1 | -1
37 | 2 | 2 | 0
37 | 3 | 3 | 0
38 | 1 | 4 | 0,5
38 | 2 | 5 | 5
38 | 3 | 6 | 1

Et il faudrait que je recupere :
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 | 1 | 1 | -1
37 | 2 | 2 | 0
37 | 3 | 3 | 0
37 | 4 | NULL | NULL
38 | 1 | 4 | 0,5
38 | 2 | 5 | 5
38 | 3 | 6 | 1
38 | 4 | NULL | NULL

Donc j'ai 4 règles de définis en base, mais mes cartes en utilise que trois, il me faut absolument récupérer les infos NULL pour la règle 4.

[CinePhil]

Au temps pour moi ! Il faut déporter la condition de restriction du WHERE vers la condition de jointure puisqu'elle porte sur la table de droite, comme je l'explique dans mon blog !

Code :

1
2
3
4
5
SELECT r.regle_ID, l.limitation_ID, l.limitation_valeur
FROM Regle r
LEFT OUTER JOIN Limitation l 
    ON l.regle_ID = r.regle_ID
    AND l.carte_ID = 1

Quoique, apparemment maintenant ce n'est plus pour une carte mais plusieurs ?
Tu veux une restriction sur les cartes ou tu les veux toutes ?

[Marty000]

Merci beaucoup,
en effet, on se rapproche du résultat que j'aimerais avoir, j'ai encore un petit soucis.
J'ai modifié la requête pour qu'elle puisse me retourner également le numéro de la carte, et ce, pour l'ensemble des cartes :

Code :

1
2
3
4
SELECT l.carte_ID, r.regle_ID, l.limitation_ID, l.limitation_valeur
FROM Regle r
LEFT OUTER JOIN Limitation l 
    ON l.regle_ID = r.regle_ID

J'ai enlevé l.carte_ID = 1 pour avoir une liste, seulement, j'obtiens toutes mes cartes avec leurs 3 limitations (avec les fameuses 3 règles utilisées) et à la fin, une seule ligne pour la règle 4 :
carte_ID | regle_ID | limitation_ID | limitation_valeur
NULL | 4 | NULL | NULL

Et il me faudrait
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 | 4 | NULL | NULL
38 | 4 | NULL | NULL
...

[aieeeuuuuu]

Bonjour

Essaie ceci :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT l.carte_ID, r.regle_ID, l.limitation_ID, l.limitation_valeur
FROM Regle r
CROSS JOIN (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) AS listLim(limitation_ID)
LEFT OUTER JOIN Limitation l 
    ON l.regle_ID = r.regle_ID
    AND l.limitation_ID = listLim.limitation_ID

Tu peux aussi remplacer la pseudo table ListLim par un requete distinct dans ta table limitation, voire mieux, avoir une vraie table avec la liste exhaustive des limitations possibles (table que tu as surement déjà ?!? )

[Marty000]

Bonjour aieeeuuuuu et merci à toi aussi.
Seulement le résultat est un peu différent de ce que je veux :

carte_ID | regle_ID | limitation_ID | limitation_valeur
37 1 1 -1
NULL 1 NULL NULL
NULL 1 NULL NULL
38 1 4 0,5
NULL 2 NULL NULL
37 2 2 0
NULL 2 NULL NULL
NULL 2 NULL NULL
NULL 3 NULL NULL
NULL 3 NULL NULL
37 3 3 0

En effet, j'ai une table qui contient la liste de toutes les limitations possibles pour une liste déroulante, c'est la valeur qu'il y a dans le champ limitation_valeur

[aieeeuuuuu]

Arff, en effet, je suis allé un peu vite, et j'ai pris le problème a l'envers. Ce n'est pas les limitations qui sont parfois absentes, mais les règles
Donc :

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT carte.carte_ID, listRegle.regle_ID, l.limitation_ID, l.limitation_valeur
FROM carte
CROSS JOIN (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) AS listRegle(regle_ID) 
LEFT OUTER JOIN Limitation l
  ON l.carte_ID = carte.carte_ID
  AND l.Regle_ID = listRegle.regle_ID

[Marty000]

Super aieeeuuuuu !
Merci, c'est exactement ce dont j'avais besoin !

[Marty000]

Arf !
J'ai été un peu trop rapide.
En fait, il y a un toujours un soucis, la solution proposée n'est pas dynamique.
En effet, si j'ajoute une 5e règle, et une 6e, et une 7e... Et ainsi de suite jusqu'à la Xe règle, la solution proposée ne marche plus vu qu'avec cette partie :

Code :

1
2
3
4
5
6
CROSS JOIN (
  SELECT 1 AS T1 UNION ALL
  SELECT 2 AS T2 UNION ALL
  SELECT 3 AS T3 UNION ALL
  SELECT 4
) AS listRegle(regle_ID)

L'identifiant de la règle, regle_ID, est statique jusqu'à la règle de regle_ID à 4...
En gros, ma carte peut avoir 3 limitations de définies, avec la règle d'id 17, la règle d'id 35, et la règle d'id 999.
Et dans ce cas, il faudrait faire :

Code :

1
2
3
4
5
6
7
8
CROSS JOIN (
  SELECT 1 AS T1 UNION ALL
  SELECT 2 AS T2 UNION ALL
  SELECT 3 AS T3 UNION ALL
  SELECT 4 UNION ALL
  ...
  SELECT X
) AS listRegle(regle_ID)

D'où un soucis

[CinePhil]

Et comme ça ?

Code :

1
2
3
4
SELECT c.carte_ID, r.regle_ID, l.limitation_ID, l.limitation_valeur
FROM limitation l
RIGHT OUTER JOIN carte c ON c.carte_ID = l.carte_ID
RIGHT OUTER JOIN regle r ON r.regle_ID = l.regle_ID

[Marty000]

Merci aussi pour cette solution, mais elle ne fonctionne pas pour moi non plus.
Je récupère ces informations :

carte_ID | regle_ID | limitation_ID | limitation_valeur
NULL 4 NULL NULL
37 5 33998 1
37 1 1 -1
37 2 2 0
37 3 3 0
38 1 4 0,5
38 2 5 5
38 3 6 1

Je me rends compte que je vous ai induite en erreur avec mes "tableaux" il ne s'agit pas de regle_ID mais du numéro de la règle utilisée, sachant que j'en ai 4 au maximum.

Le vrai tableau serait
carte_ID | NUMERO DE REGLE | regle_ID | limitation_ID | limitation_valeur
37 1 1 1 -1
37 2 2 2 0
37 3 3 3 0
37 4 NULL NULL
38 1 1 4 0,5
38 2 2 5 5
38 3 3 6 1
38 4 5 "Limitation_ID" "une valeur"

Dans cet exemple, la carte 38, a dans la 4e règle, la règle d'identifiant 5.
Excusez moi pour cette erreur.

[Waldar]

Vous nous dites ce que vous voulez obtenir, mais sauf erreur vous ne donnez pas les données de départ, donc au final, ça ne permet pas de faire un bon test.

Si le jeu initial est trop volumineux pour être lisible sur le forum, il suffit de le réduire aux différents cas de figures possibles : et ce de façon exhaustive.

[aieeeuuuuu]

Envoyé par Marty000

Arf !
J'ai été un peu trop rapide.
En fait, il y a un toujours un soucis, la solution proposée n'est pas dynamique.

hé bien remplace la cette pseudo table par une sous requete qui va te fournir la liste des Règles possible, par exemple:

Code sql :

1
2
3
4
5
 
CROSS JOIN (
    SELECT DISTINCT regle_ID 
    FROM Regle
) AS listRegle(regle_ID)

[Marty000]

Merci de votre remarque Waldar.
Voici mon jeu de données pour deux cartes 37 et 38.
La table carte avec une colonne carte_ID (il y a d'autres colonnes mais à but information, des dates)

La table Limitation voici les informations :
Limitation_ID | Limitation_valeur | Carte_ID | Regle_ID
1 -1 37 1
2 0 37 2
3 0 37 3
4 0,5 38 1
5 5 38 2
6 1 38 3
33998 1 37 5

La table Regle avec un colonne regle_ID (comme pour les cartes avec des colonnes dates en plus) : 1,2,3,4,5

[Marty000]

Oui aieeeuuuuu, j'avais pensé à ça après, mais comme je l'explique un peu plus haut, je me suis mal exprimé sur le règle_ID, en fait c'est vraiment :
carte_ID | NUMERO DE REGLE | regle_ID | limitation_ID | limitation_valeur
37 1 1 1 -1
37 2 2 2 0
37 3 3 3 0
37 4 NULL NULL
38 1 1 4 0,5
38 2 2 5 5
38 3 3 6 1
38 4 5 "Limitation_ID" "une valeur"

Que j'aimerais avoir.
Et la solution que vous me proposez me retourne :
carte_ID | regle_ID | limitation_ID | limitation_valeur
37 1 1 -1
37 2 2 0
37 3 3 0
37 5 33998 1
37 4 NULL NULL
38 1 4 0,5
38 2 5 5
38 3 6 1
38 4 NULL NULL
38 5 NULL NULL

Désolé

[aieeeuuuuu]

et d'ou provient le "numéro de regle" ? de la table regle ? dans ce cas faites la jointure dessus au lieu de la faire sur l'ID

[Marty000]

Le numéro de règle c'est juste un numéro "d'emplacement" qui n'a pas d'existence propre en base.
En fait grâce à cette vue, j'utilise une procédure stockée pour concaténer les différentes valeurs :
la limitation_valeur en 1, avec la limitation_valeur en 2
et le limitation_valeur en 3 avec la limitation_valeur en 4

Si c'est nul en 3, seul le 4 est affiché, mais ce traitement est fait après.

Quoi qu'il arrive, une carte a toujours 4 règles de définis après c'est soit règle 1, règle 2, règle 17, nul, soit règle 2, règle 3, règle 4, règle 5, soit nul, nul, nul, règle 89, soit nul, nul, nul, nul.