Discussion :

[cana13]

Bonjour à tous,

je m'arrache les cheveux sur un script SQL que je n'arrive pas à faire :

j'ai 3 tables :
une table Temps (1 colonne date qui est la clé)
une table vehicule (1 colonne code_vehicule qui est la cle et une designation)
une table livraison (1 colonne code_livraison, 1 colonne date_livraison, 1 colonne Code_vehicule)

Je veux faire une requete qui me donne, pour une fourchette de date, les vehicules disponibles (non presents sur une livraison).

exemple :
ma table vehicule :
V1 vehicule 1
V2 vehicule 2
V3 vehicule 3
V4 vehicule 4

ma table livraison :
01/06/09 Liv 1 Vehicule V1
01/06/09 Liv 2 Vehicule V2
02/06/09 Liv 1 Vehicule V1
02/06/09 Liv 2 Vehicule V3

et le resultat escompté : Liste des vehicules disponibles :
01/06/09 V3
01/06/09 V4
02/06/09 V2
02/06/09 V4

Mon niveau SQL est un peu faible pour ce genre de problematique, j'ai trituré les LEFT OUTER JOIN dans tous les sens mais je m'en sors pas !

Merci d'avance de votre aide

[CinePhil]

Donne nous la requête que tu as essayé de faire et précise quel est ton SGBD.

[cana13]

Ben j'en ai essayé 36 depuis ce matin, la derniere en date :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT     Commun.trTemps.date,
 Transp.v_tfTournee.Code_tournee,
 Transp.v_tfTournee.Date_tournee,
 Transp.v_tfTournee.Code_vehicule
FROM  Commun.trTemps LEFT OUTER JOIN
      Transp.v_tfTournee ON Commun.trTemps.date = Transp.v_tfTournee.Date_tournee
LEFT OUTER JOIN
Transp.trVehicule ON Transp.v_tfTournee.Code_vehicule <> Transp.trVehicule.Code_vehi
WHERE     (Commun.trTemps.date >= '01/06/09') AND (Commun.trTemps.date <= '08/06/09')

je suis sous SQL SERVER 2005

[Waldar]

Commencer par faire un produit cartésien entre votre table Temps et votre table de véhicules.

[Snipah]

Essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
       t.date,
       v.code_vehicule
FROM Commun.trTemps t, Transp.trVehicule v
WHERE NOT EXISTS
      (
       SELECT 1 
       FROM Transp.v_tfTournee l
       WHERE l.code_vehicule = v.code_vehicule
       AND l.date_tournee = t.date
      )
AND t.date BETWEEN '01/06/09' AND '08/06/09' 
ORDER BY t.date, v.code_vehicule

EDIT : devancé par Waldar, et en admettant que je me sois pas planté, je donne la solution toute faite, c'est pas très pédagogique...

[CinePhil]

Pour commencer, j'espère que ta table livraison (la table Transp.v_tfTournee dans ta requête je suppose ?) a plutôt l'aspect suivant :

01/06/09 Liv 1 Vehicule V1
01/06/09 2 V2
02/06/09 1 V1
02/06/09 2 V3

Si les textes 'Liv' et 'Véhicule' figurent dans la table, il sera plus compliqué et surtout beaucoup moins performant d'établir l'égalité entre les identifiants.

Pourquoi avoir mis une inégalité (<>) dans la seconde jointure ?

Si j'ai bien compris, tu cherches la liste des tous les véhicules ne figurant pas dans la table des livraisons pour chaque jour d'une plage de dates ?

Il faut donc à la fois considérer toutes les dates de la période et tous les véhicules, puis chercher les lignes de livraison qui n'auront pas de correspondance.

Je simplifie le contenu des tables ci-dessous :

Table 'Commun.trTemps'
01/06/09
02/06/09
...

Table 'Transp.trVehicule'
V1
V2
V3
V4

Table 'Transp.v_tfTournee'
01/06/09 V1
01/06/09 V2
02/06/09 V1
02/06/09 V3

Pour prendre toutes les dates et tous les véhicules, j'ai besoin d'une jointure externe gauche des dates vers les livraisons et d'une jointure externe droite des livraisons vers les véhicules.

La requête avec des alias pour plus de lisibilité donne ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT t.date, v.Code_Vehi
FROM Commun.trTemps AS t
LEFT OUTER JOIN Transp.v_tfTournee AS vtf ON t.date = vtf.Date_tournee
  RIGHT OUTER JOIN Transp.trVehicule AS v ON vtf.Code_vehicule = v.Code_vehi
WHERE t.date BETWEEN '01/06/09' AND '08/06/09'
  AND vtf.Date_Tournee IS NULL

Les jointures devraient donner ce genre de résultat (simplifié) :
Date ---------- Livraison --------- Véhicule
01/06/09 ----- 01/06/09 V1 ------- V1
01/06/09 ----- 01/06/09 V2 ------- V2
01/06/09 ----- NULL ---------------- V3
01/06/09 ----- NULL ---------------- V4
02/06/09 ----- 02/06/09 V1 ------- V1
02/06/09 ----- NULL ---------------- V2
02/06/09 ----- 02/06/09 V3 ------- V3
02/06/09 ----- NULL ---------------- V4
...

Comme on ne retient que les lignes où la date de livraison est NULL (on aurait pu aussi choisir code_vehicule IS NULL), on devrait obtenir le résultat suivant :
Date ---------- Véhicule
01/06/09 ----- V3
01/06/09 ----- V4
02/06/09 ----- V2
02/06/09 ----- V4

[cana13]

Super j'ai compris !

Suite à l' indication de waldar j'ai compris le principe qu'il fallait mettre en oeuvre (produit cartesien temps/vehicule et en faire l'ecart avec mes tournées).
Pour le produit cartesien c'est bon mais je bloquais ensuite car je tentais de partir sur une piste de type Jointure et j'ignorais que l'on pouvait faire un autre SELECT dans la clause WHERE.

Merci beaucoup
a+

[cana13]

Cinephil, tout d'abord oui, tes precisions sur les tables sont exactes.

ensuite j'ai essayé ta requete mais elle me renvoie 0 resultat. Mais je pense que lorsqu'on dit

AND vtf.Date_Tournee IS NULL

ça ne peut pas marcher car dans ma table je n'ai jamais de ligne avec une date null ou un vehicule null ...

et lorsque je l'enleve de la requete, j'obtiens toutes les lignes de ma table tournée mais pas les lignes ou ou il n'y a pas de livraison soit :

Date ---------- Livraison --------- Véhicule
01/06/09 ----- 01/06/09 V1 ------- V1
01/06/09 ----- 01/06/09 V2 ------- V2
02/06/09 ----- 02/06/09 V1 ------- V1
02/06/09 ----- 02/06/09 V3 ------- V3

[CinePhil]

Exact, je viens de me rendre compte que mon raisonnement est faux puisque chaque date et chaque véhicule est présent au moins une fois dans la table de jointure. Au temps pour moi !