Requete sur plusieurs tables avec TOP

Bonjour,
je m'excuse par avance si le sujet n'est pas à la bonne place car il n'agit pas d'un problème pur Windev mais plus de requête SQL.

j'ai créé une requête SQL pour afficher les informations du parc machines d'un client; jusque la, tout va bien tout s'affiche correctement avec mes jointures pour les informations de modèles, fabricants, clients, villes, ... qui sont dans d'autres fichiers HFSQL; le tout est affiché dans une fenêtre Windev dans une table.

Mon problème est que le client voudrait rajouter dans cette table, les informations du dernier devis réalisé sur ces machines.
J'ai essayé de remplir ma table avec ma précédente requête puis de parcourir toutes les lignes de la table et de chercher le dernier devis réalisé sur cette machine en faisant un SELECT TOP 1 FROM Devis; ça fonctionne mais c'est très lent ( la base machine fait 34 000 lignes).

Y aurait-il un moyen de combiner les deux requêtes à savoir le

Code:

---

SELECT * FROM Machine

---

et le

Code:

---

SELECT TOP 1 * FROM DEVIS

---

de façon à ce que pour chaque ligne de ma première requête, j'obtienne les informations du dernier devis associé?

J'ai regardé au niveau des UNION ou des sous requetes, mais ça ne correspond pas à ce que je cherche; je ne veux pas ajouter des lignes au résultat, mais des colonnes.

je vous remercie

Bonjour,
La recherche du dernier, plus grand… revient souvent dans la section SQL de ce forum.
En premier il faut déterminer le critère de tri, puis se dire que le dernier est celui pour le quel on n'en trouve pas "après".
Ca peut se faire avec un NOT EXISTS, une jointure externe…
Par exemple (datesaisie est un dateheure):

Code:

---

1 2 3 4 5 6

select machine.*,d1.* from machine inner join devis as d1 on d1.idmachine = machine.id left outer join devis as d2 on d2.idmachine = machine.id and d2.datesaisie > d1.datesaisie where d2.idmachine is null

---

Accessoirement, un SELECT TOP 1 sans ORDER BY ne garantit aucunement d'avoir le dernier devis, tout va dépendre de la stratégie choisie par le moteur, stratégie qui peut varier dans le temps.
Tatayo.

La requête proposée avec le outer-join est correcte, mais je trouve que le not-exists est plus facile à lire pour un humain :

Code:

---

1 2 3 4

select machine.*,d1.* from machine inner join devis as d1 on d1.idmachine = machine.id where not exists (select * from devis as d2 where d2.idmachine = machine.id and d2.datesaisie > d1.datesaisie)

---

En terme de performance, ça doit être très similaire.

Bonjour,
je te remercie pour ta réponse.

En relisant ce que j'ai écrit, je me rends compte qu'il manque des éléments:
tout d'abord, mes fichiers de données:
j'ai mon fichier Machine:
- IdMachine (identifiant unique)

mon fichier Devis (qui contient les informations générales du devis)
- idDevis (identifiant unique)
- date de création

mon fichier DevisDetail (qui contient les informations des "lignes" du devis, numéro de série machine, ....)
-idDevisDetail (identifiant unique)
-idDevis (pour rattacher cette ligne au devis)
-idMachine (pour rattacher au fichier Machine)

je souhaiterais donc quand je fais une recherche quelconque dans mon fichier Machine, récupérer les informations des machines, et pour chaque machine, récupéré l'id du dernier devis dont l'idMachine apparait dans le DevisDetail. La sélection du TOP 1 sera faite sur la date de création indiquée dans le fichier Devis triée par un ORDER BY Devis.DateCreation DESC

si une machine n'a pas de devis, elle doit apparaitre dans le résultat de la recherche, juste que se sera Null au niveau des informations du devis.

J’espère avoir apporté plus de précision sur la structure de ma base et sur ma demande.

On veut que toutes les machines apparaissent. Donc :
select * from imachine ....

On veut par aiileurs une espèce de table virtuelle, avec la dernière occurrence pour chaque machine :

select id_machine , max (d.date_Creation) as Most_recent_date from DevisDetail dd
inner join devis D on D.id_devis = dd.id_devis
group by id_machine

Et on va combiner tout ça :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select from id_machine M outer join ( select dd.id_machine , max (d.date_Creation) as date_1 from DevisDetail dd inner join devis D on D.id_devis = dd.id_devis group by id_machine ) X1 on x1.id_machine = m.id_machine outer join ( select dd.id_machine , d.d_devis , d.date_Creation) from DevisDetail dd inner join devis D on D.id_devis = dd.id_devis ) X2 on X2.id_machine = X1.id_machine and x2.date_creation = x1.date_1

---

A mon avis, on devrait être assez proche du résultat.
Je pars du principe que pour une machine et une date, on a un seul devis.

Si au lieu d'utiliser HFSQL, tu utilises des trucs comme SQL Server, ou ORACLE ( ce qui est tout à fait possible avec Windev) , il y a des commandes SQL beaucoup plus élégantes. Ici, la requête est bien pourrie, mais de ce que je connais de HFSQL, on ne pourra pas faire beaucoup mieux.

Bonjour tbc92,
je te remercie pour ta réponse.

Je ne comprends pas ce que sont les X1 et X2 dans ta requête.

Il faut savoir qu'il peut y avoir plusieurs devis pour une même machine (ce sont des devis de vérification périodique; ces vérifications sont faites tous les ans ou tout les 2 ans selon le type de machine [à la manière d'un contrôle technique pour les véhicules]).
Cette requête va me permettre d'indiquer à l'utilisateur lorsqu'il visualise son parc machine quelles machines ont fait l'objet d'un devis récemment et celles pour lesquelles il doit faire un devis.

Voila la solution sur laquelle j'étais parti mais ça ne fonctionne pas:

Code:

---

1 2 3 4 5

SELECT M.IdMachine, ... (toutes les données que je veux afficher sur le client associé à la machine, la ville, le département, ...) ..., D.NumeroDevis, D.DateCreation FROM Machine M LEFT OUTER JOIN ... (toutes les jointures nécessaires sur les fichiers Client, Ville, ...) LEFT OUTER JOIN DevisDetail DD ON M.IdMachine = DD.IdMachine LEFT OUTER JOIN Devis D ON Devis.IdDevis = (SELECT TOP 1 D1.IdDevis, D1.DateCreation FROM Devis D1 WHERE D1.IdDevis = DD.IdDevis ORDER BY D1.DateCreation DESC)

---

En fait, le but est pour chaque résultat de la requete SELECT ... FROm Machine, je fasse une recherche pour trouver tous les IdDevis des DevisDétails qui contiennent l'IdMachine et que je prenne IdDevis du Devis le plus récent

Le résultat de cette requête est ensuite affiché dans une table avec la fonction SQLTable, c'est pour ça que j'ai besoin de récupérer aussi les informations du client, ...
Le tout sera affiché de la sorte dans la table:

IdMachine	Numéro compte client	Nom du client	...	Numéro dernier Devis	Date dernier devis
num1	0001	Client1	...	2018.08.203	05/08/2018
num2	0001	Client1	...	2018.08.203	05/08/2018
num3	0001	Client1	...	2017.05.045	24/05/2017
num4	0002	Client2	...	2018.07.002	01/07/2018

Ce qui permettra à l'utilisateur de voir que le dernier devis fait pour la machine num3 date de plus d'un an et donc qu'il faut refaire un devis; les machines num1, num2 et num4 ont un devis récent, pas besoin créer un devis pour ces machines pour l'instant.

Je tiens à préciser que la récupération des informations machine, client, ville, ... ne me pose pas de soucis, c'est juste la récupération du devis qui est problématique.

Au déébut, tu parlais de 2 tables ( devis et machines). C'était clair, tu as eu une réponse.
Puis tu parlais de 3 tables, (devis machine et devis détail). La demande était claire, je pense que ma réponse était correcte.
Maintenant, tu parles d'une table avec des clients.
Quelle sera la prochaine table ?

Je suis désolé, je pensais au départ qu'en ne parlant que de deux tables, je n'aurais qu'à adapter la solution à mon problème (comme ça je progresse en même temps), sauf que je me suis rendu compte que ma demande était trop vague, j'ai donc rajouté la table DevisDetail.

Les autres tables (clients, ...) ne sont là que pour montrer le contexte de ma requête et montrer que je ne cherche pas que l'Id de la machine mais d'autres infos; comme je l'ai mis dans les différentes modifications de mon dernier message, les recherches sur les autres tables (client, ...) fonctionnent bien, ça ne me pose pas de problème.
Le seul problème que j'ai, c'est comment rajouter à ma requête la recherche du dernier devis réalisé pour la machine.

J'aurais du être plus clair et commencer par mon dernier message dès le début et je ne vous aurais pas fait perdre votre temps, désolé.

Promis, il n'y aura pas d'ajout de nouvelles tables, les 3 seules qui nous intéressent sont les table Machine, Devis et DevisDetail.

Encore merci pour les réponses que vous m'avez apportées jusqu'à présent.

J'ai utilisé la méthode avec les tables virtuelles et voici ce que ça donne; je ne fait pas d'alias sur les noms des tables car je trouve que c'est moins lisible

Code:

---

1 2 3 4 5 6

SELECT Machine.IdMachine, ..., Devis.NumeroDevis, Devis.DateCreation, FROM Machine LEFT OUTER JOIN ... // les ... sont pour les autres éléments de la recherche (ne nous intéressent pas ici) LEFT OUTER JOIN (SELECT DevisDetail.IdMachine, Max(Devis.DateCreation) AS DEVIS_Date FROM DevisDetail LEFT OUTER JOIN Devis ON Devis.IdDevis = DevisDetail.IdDevis GROUP BY DevisDetail.IdMachine) X1 ON X1.IdMachine= Machine.IdMachine LEFT OUTER JOIN (SELECT DevisDetail.IdMachine, Devis.IdDevis, Devis.DateCreation FROM DevisDetail LEFT OUTER JOIN Devis ON Devis.IdDevis = DevisDetail.IdDevis) X2 ON X2.IdMachine= X1.IdMachine and X2.DateCreation = X1.DEVIS_Date

---

Quand j'execute la requete, j'ai l'erreur suivante :

Citation:

---

L'alias DevisDetail est déjà utilisé

---

sauf que je ne crée pas d'alias DevisDétail, d'où peut provenir mon erreur?

je vous remercie

Edit:
j'ai essayé d'utiliser un TOP 1 avec ORDER BY dans ma table virtuelle :

Code:

---

1 2

SELECT Machine.IdMachine, Devis.NumeroDevis, Devis.DateCreationFROM Machine LEFT OUTER JOIN ... LEFT OUTER JOIN (SELECT TOP 1 DevisDetail.IdMachine, Devis.DateCreation FROM DevisDetail LEFT OUTER JOIN Devis ON Devis.IdDevis = DevisDetail.IdDevis ORDER BY Devis.DateCreation DESC) X1 ON X1.IdMachine= Machine.IdMachine

---

et j'obtiens l'erreur

Citation:

---

Mot ORDER inatendu

---

Je n'ai pas le droit de faire de tri dans une sous requête?

La réponse est dans ton message : tu ne mets pas d'alias sur les tables. Tu as une requete qui lit Devis, devisdetail, et devisdetail.
Le requêteur voit que tu utilises 2 fois la table devisDetail, sans rien pour les différencier. Il est perdu.

Bonjour;

- Les grands soucis du programmeur c'est le temps et l'espace mémoire.
- Ton problème à ce que j'ai compris c'est le temps.
- Les bases de données ont été créer pour minimiser la redondance mais on ne peut éliminer cette dernière.

Alors je te propose d'ajouter une rubrique au niveau du Fichier Machine "ID_Dernier_Devis" qui sera mise à jour à chaque nouveau devis pour la machine concernée

Bon Dev.

Je vous remercie pour vos réponses

@ tbc92:
j'ai rajouté des alias en prenant soin de les créer différents selon la sous requête sinon, j'avais la même erreur.
voila ce que ça donne:

Code:

---

1 2 3 4

SELECT M.IdMachine, Devis.NumeroDevis, Devis.DateCreation FROM Machine M LEFT OUTER JOIN ... LEFT OUTER JOIN (SELECT DD1.IdMachine, Max(D1.DateCreation) AS DEVIS_Date FROM DevisDetail DD1 LEFT OUTER JOIN Devis D1 ON D1.IdDevis = DD1.IdDevis GROUP BY DD1.IdMachine) X1 ON X1.IdMachine= Machine.IdMachine LEFT OUTER JOIN (SELECT DD2.IdMachine, D2.IdDevis, D2.DateCreation FROM DevisDetail DD2 LEFT OUTER JOIN Devis D2 ON D2.IdDevis = DD2.IdDevis) X2 ON X2.IdMachine = X1.IdMachine and X2.DateCreation = X1.DEVIS_Date)

---

et là, je rencontre plusieurs problèmes:
le premier: j'obtiens l'erreur

Citation:

---

Le fichier Devis n'existe pas dans la clause FROM

---

en second, si je transforme Devis.NumeroDevis et Devis.DateCreation par D1.NumeroDevis et D1.DateCreation, j'obtiens l'erreur

Citation:

---

Le fichier D1 n'existe pas dans la clause FROM

---

Si je supprime la recherche du numéro de devis et de la date de création du devis, ça fonctionne, mais je ne récupère pas ces infos, ce qui est embêtant car c'était le but.
Est-ce que je dois rajouter un

Code:

---

LEFT OUTER JOIN DevisDetail DD ON M.IdMachine = DD.IdMachine LEFT OUTER JOIN Devis D ON DD.IdDevis = D.IdDevis

---

avant les sous requêtes et que je mette D.NumeroDevis, D.DateCreation dans mon SELECT global?

@fouedusa:
c'est la méthode que j'avais prévu d'utiliser si jamais l'autre méthode ne fonctionne pas car il y a beaucoup de points à prendre en compte.


edit:
alors l'ajout des left join pour les Devis et DevisDetail a supprimé les erreurs mais le problème est que la sélection du dernier devis ne fait plus; si une machine apparait sur 2 devis (un en 2017 et un en 2018 par exemple), elle apparait deux fois dans les résultats.

Tu ne peux pas reprendre dans un SELECT des tables qui sont dans des sous-requêtes.
Donc la table DEVIS doit apparaître quelque part dans la requête (hors sous-requête).

Tu peux aussi faire ainsi, avec une sous-requête corrélée:

Code:

---

1 2 3 4 5 6 7

select * // A remplacer par la liste des colonnes from machine M LEFT OUTER JOIN … // Toutes les jointures dont tu as besoin LEFT OUTER JOIN DevisDetail DD ON M.IdMachine = DD.IdMachine LEFT OUTER JOIN Devis D ON Devis.IdDevis = d.idDevis Where d.idDevis is null or not exists(select 1 from DevisDetail as DD2 inner join Devis as D2 on DD2.idDevis = D2.IdDevis and DD2.IdMachine = M.IdMachine and DD2.DateCreation > DD.DateCreation)

---

Ainsi tu as toutes les machines sans devis, ou avec le dernier devis en date.

Tatayo.

bonjour,

Code:

---

(SELECT DD1.IdMachine, Max(D1.DateCreation) AS DEVIS_Date FROM DevisDetail DD1 LEFT OUTER JOIN Devis D1 ON D1.IdDevis = DD1.IdDevis GROUP BY DD1.IdMachine) X1 ON X1.IdMachine= Machine.IdMachine

---

toutes les colonnes faisant référence à cette sous-requête devra se faire ainsi :

• X1.IdMachine
• X1.DEVIS_Date
• etc.

Code:

---

(SELECT DD2.IdMachine, D2.IdDevis, D2.DateCreation FROM DevisDetail DD2 LEFT OUTER JOIN Devis D2 ON D2.IdDevis = DD2.IdDevis) X2 ON X2.IdMachine = X1.IdMachine and X2.DateCreation = X1.DEVIS_Date)

---

idem pour celle-ci mais avec X2