Jointures externes pour creer un grand tableau

[cjordi]

Bonjour,

J'ai une requête sur laquelle je me casse la tête depuis plusieurs heures, et je pense avoir besoin de vous ^^.

Ma base de donnée est composé de 5 tables :

vous trouverez la base de donnée en pièce jointe.

Je souhaite creer la requete avec les résultats suivant :

colonne n°1 : tool_name
colonne n°2 : indic_name
colonne n°3 : data_time
colonne n°4 : data_value

Je veux que pour chaque tool_name, j'ai tous les indic_name même si il n'y a pas de data_value. ( mettre NULL si absence de résultats )
Chaque tool_name à une date différente, je veux récupérer la derniere date pour chaque tool_name.

Enfaite comment ça marche :
Sur chaque tool_name, je viens passer 2 recipes.
La recipe me permet de récupérer des valeurs sur des indic_names.

Je veux afficher un grand tableau de ce type avec les derniers résultats pour chaque tool_name :


Alors j'ai essayé de créer la requête et avec celle-ci il me manque 6 lignes...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT DISTINCT t.tool_key, t.tool_name, d.data_time,  i.indic_name, d.data_value
FROM
          (SELECT ti.tool_key, ti.tool_name, MAX(di.data_time) AS data_time
          FROM tool AS ti
          LEFT JOIN DATA AS di USING (tool_key)
          RIGHT JOIN indicator AS ii USING (indic_key)
          WHERE recipe = "FINGERPRINT2.rcp"
          AND critical = "yes"
          AND (data_time = di.data_time OR data_time IS NULL)
          GROUP BY tool_name) t
LEFT JOIN DATA AS d USING (tool_key)
RIGHT JOIN indicator AS i USING (indic_key)
WHERE (d.data_time = t.data_time OR d.data_time IS NULL)
AND recipe = "FINGERPRINT2.rcp" 
AND critical = "yes"
ORDER BY indic_name, tool_name

avec la recette FINGERPRINT2.rcp et critical = yes, j'ai 4 indic_name pour chaque tool_name. Il y a 26 tool_name, je devrai avoir 26x4 = 104 lignes de résultats. Hors je n'en ai que 98.... :



Merci de votre aide.

[CinePhil]

Je veux que pour chaque tool_name, j'ai tous les indic_name même si il n'y a pas de data_value.

Le problème de ton schéma est que l'indicateur est relatif à un parc mais pas forcément à tous les outils du parc.

Sur chaque tool_name, je viens passer 2 recipes.

Alors l'indicateur devrait avoir pour clé étrangère le tool et non pas le park !

je veux récupérer la derniere date pour chaque tool_name

Code :

1
2
3
4
5
SELECT t.tool_name, 
    MAX(d.data_time) AS last_date
FROM TOOL t
LEFT OUTER JOIN DATA d ON d.tool_key = t.tool_key
GROUP BY t.tool_name

Sur chaque tool_name, je viens passer 2 recipes.
...
même si il n'y a pas de data_value

Doit-on comprendre que la génération d'une ligne dans DATA est systématique mais que la colonne data_value peut être à NULL ?

Si c'est le cas, essaie cette requête :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT tmp.tool_name, i.indic_name, tmp.last_date, d.data_value
FROM 
(
    SELECT t.tool_key, t.tool_name, 
        MAX(d.data_time) AS last_date
    FROM TOOL t
    LEFT OUTER JOIN DATA d ON d.tool_key = t.tool_key
    GROUP BY t.tool_name
) tmp
INNER JOIN DATA d1 
    ON d1.tool_key = tmp.tool_key
    AND d1.data_time = tmp.last_date
 
    INNER JOIN INDICATOR i ON i.indic_key = d1.indic_key

[cjordi]

Ta requête m'a bien aidé !
L'idée d'utiliser un alias last_date est parfaite. J'ai bien mes 104 lignes avec les dernieres dates et derniers résultats !

Je te remercie !

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT tmp.tool_name, i1.indic_name, tmp.last_date, d1.data_value
FROM 
(
    SELECT t.tool_key, t.tool_name, 
        MAX(d.data_time) AS last_date
    FROM TOOL t
    LEFT OUTER JOIN DATA d ON d.tool_key = t.tool_key
    RIGHT JOIN INDICATOR i ON i.indic_key = d.indic_key
    WHERE recipe="FINGERPRINT2.rcp" AND critical="yes"
    GROUP BY t.tool_name
) tmp
LEFT JOIN DATA d1 
    ON d1.tool_key = tmp.tool_key
    AND d1.data_time = tmp.last_date
RIGHT JOIN INDICATOR i1 ON i1.indic_key = d1.indic_key
WHERE recipe="FINGERPRINT2.rcp" AND critical="yes"
ORDER BY indic_name, tool_name

Sinon j'ai modélisé la base de donnée de cette manière car :

Ma société est constituée de 8 areas.
Dans chaque area il y a entre 3 et 6 parcs.
Dans chaque parc il y a des dizaines de tools.

Les tools sont biens spécifiques à un type de parc.

Il y a 2 recettes qui tournent sur 1 parc et qui permet de générer des indicators pour l'ensemble des tools du parc.

data associe une valeur à un tool et à un indicator.

Peut-être qu'il y a une meilleure façon de modéliser la base ?

Merci.