Conception / Requête SQL

Version imprimable

Bonjour à tous !

La situation
ADO / Access
J'ai 2 tables :
- T_CLIENTS (CLI_ID, CLI_Nom, CLI_Prenom, ...)
- T_ACHATS (ACH_ID, ACH_Date, ACH_Id_Client, ACH_Montant, ACH_Commentaire, ...)

Le problème
Je souhaite récupérer la liste de tous les clients et de leur dernier commentaire en date. J'y arrive par le biais de deux requêtes successives : 1) récupération des noms et prénoms sur la table T_CLIENTS puis, 2) à l'affichage dans un StringGrid, pour chaque client la deuxième requête récupère ACH_Commentaire pour lequel ACH_Date est le plus grand soit :
Code:

1 2 3 4 5 strSQL1 := 'SELECT CLI_ID, CLI_Nom, CLI_Prenom FROM T_CLIENTS ORDER BY CLI_Nom, CLI_Prenom'; strSQL2 := 'SELECT ACH_Commentaire FROM T_ACHATS WHERE ACH_Id_Client = :PIdClient1 AND ACH_Date >= ALL ' + '(SELECT ACH_Date FROM T_ACHATS WHERE ACH_Id_Client = :PIdClient2)';
ce qui donne :
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 with ADOQry1 do begin Close; SQL.Text := strSQL1; Active := true; if RecordCount > 0 then begin Fields.DataSet.FindFirst; while not Fields.DataSet.Eof do begin with ADOQry2 do begin Close; SQL.Text := strSQL2; ParamCheck := true; Parameters.ParamByName('PIdClient1').Value := ADOQry1.FieldByName('CLI_ID').AsString; // résultat de la requête #1 Parameters.ParamByName('PIdClient2').Value := ADOQry1.FieldByName('CLI_ID').AsString; Active := true; if RecordCount > 0 then begin Fields.DataSet.FindFirst; etc ....
Les questions
Ce code fonctionne, mais je le trouve lourd !

>> Cette structure de requête vous semble-t-elle adaptée ?
>> Existe-t-il un moyen de combiner les 2 requêtes avec une jointure ? Est-ce judicieux ?

NB : pour info : le nombre de clients retourné est faible (<100) et le nombre de lignes d'achat par client à prendre en compte par le moteur SQL aussi (50 en moyenne).

Merci d'avance pour vos commentaires. ;)

13/02/2012, 09h00
SergioMaster
Bonjour , oui la requête est 'lourde'

Avec Access je vais pas trop m'avancer ( je ne suis pas un fan ) mais je suis sur qu'il est possible d'améliorer la requête 2

Sous Firebird j'aurais fait
Code:

1 2 SELECT FIRST 1 ACH_Commentaire FROM T_ACHATS WHERE ACH_Id_Client = :PIdClient1 ORDER BY ACH_Date DESC
pour obtenir uniquement le 'dernier enregistrement' en date
l'astuce étant dans le ORDER BY surtout , le FIRST 1 étant pour limiter le nombre d'enregistrements au 1° (pour optimiser la vitesse : un index sur la date)

Déjà ceci devrait alléger le code :roll:

Maintenant , comme c'est le matin , la caféine n'est pas encore arrivé au cerveau , mais je suis sur qu'une seule requête peut faire l'affaire

Bonjour,

Je ne sais pas si Access supporte les jointures.

Mais avec Firebird, j'aurai créer la requête ainsi
Code:

1 2 3 4 5 6 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, MAX(ACH_Date) FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client GROUP BY C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire ORDER BY CLI_Nom, CLI_Prenom
A parfaire, car je n'ai pas tester. J'ai une crainte que Ach_Commentaire conduise à présenter plusieurs lignes. Si c'est le cas, il conviendra de modifier la requête.

Il y a une meilleur solution avec CTE : Common Table Expression, mais là, je ne pense pas que Access accepte.

Ne pas oublier non plus les solutions avec deux vues.

Maintenant, il faut adapter cela à Access.

Bon courage

Citation:
Envoyé par seabs
Bonjour,

Je ne sais pas si Access supporte les jointures.

Mais avec Firebird, j'aurai créer la requête ainsi
Code:

1 2 3 4 5 6 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, MAX(ACH_Date) FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client GROUP BY C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire ORDER BY CLI_Nom, CLI_Prenom
A parfaire, car je n'ai pas tester.

Il y a une meilleur solution avec CTE : Common Table Expression, mais là, je ne pense pas que Access accepte.

Ne pas oublier non plus les solutions avec deux vues.

Maintenant, il faut adapter cela à Access.

Bon courage
Ta requête ne peut fonctionner, pour chaque commentaire différent du client, tu retournes une ligne avec la date max du dernier commentaire.

Sinon oui, Access supporte les jointures :mrgreen:

Bonjour,
Citation:
Je ne sais pas si Access supporte les jointures.

Mais avec Firebird, j'aurai créer la requête ainsi
Code:

1 2 3 4 5 6 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, MAX(ACH_Date) FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client GROUP BY C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire ORDER BY CLI_Nom, CLI_Prenom
A parfaire, car je n'ai pas tester. J'ai une crainte que Ach_Commentaire conduise à présenter plusieurs lignes. Si c'est le cas, il conviendra de modifier la requête.
Effectivement, après relecture, j'avais ajouter un complément pour indiquer le point négatif de ma requête.

Je viens d'établir une nouvelle présentation que j'ai pris le temps de tester.
Code:

1 2 3 4 5 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, ACH_DATE FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client WHERE ACH_Commentaire IS NULL OR ACH_DATE = (SELECT MAX(ACH_DATE) FROM T_ACHATS WHERE ACH_Id_Client = C.CLI_ID) ORDER BY CLI_Nom, CLI_Prenom
J'ai mis ACH_Commentaire IS NULL pour présenter les clients n'ayant aucun commentaire. A supprimer si vous ne voulez pas présenter les clients ne possédant aucun commentaire.

Cette requête est parfaitement adaptable à Access, le modèle étant établi avec Firebird

Bon courage

Citation:
Envoyé par SergioMaster
Bonjour , oui la requête est 'lourde'

Sous Firebird j'aurais fait
Code:

1 2 SELECT FIRST 1 ACH_Commentaire FROM T_ACHATS WHERE ACH_Id_Client = :PIdClient1 ORDER BY ACH_Date DESC
pour obtenir uniquement le 'dernier enregistrement' en date
l'astuce étant dans le ORDER BY surtout , le FIRST 1 étant pour limiter le nombre d'enregistrements au 1° (pour optimiser la vitesse : un index sur la date)

Déjà ceci devrait alléger le code :roll:
ça a toujours l'air simple ... mais je n'y ai pas pensé sur l'instant !! FIRST 1 à remplacer par TOP 1 pour Access.

Par contre j'ai retenu la requête de seabs ;)

Citation:

Envoyé par seabs

Je viens d'établir une nouvelle présentation que j'ai pris le temps de tester

ça c'est sympa de tester avant ! comme ça je peux :zzz: !
Citation:
Envoyé par seabs
Code:

1 2 3 4 5 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, ACH_DATE FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client WHERE ACH_Commentaire IS NULL OR ACH_DATE = (SELECT MAX(ACH_DATE) FROM T_ACHATS WHERE ACH_Id_Client = C.CLI_ID) ORDER BY CLI_Nom, CLI_Prenom
J'ai mis ACH_Commentaire IS NULL pour présenter les clients n'ayant aucun commentaire. A supprimer si vous ne voulez pas présenter les clients ne possédant aucun commentaire.

Cette requête est parfaitement adaptable à Access, le modèle étant établi avec Firebird

Bon courage
Requête transposée tel quel. J'ai gardé pour l'instant le test IS NULL. A voir à l'usage. Grand :merci:

Des heures devant mon écran et je n'étais pas arrivé à l'écrire ! Merci encore pour votre rapidité !

:lahola:

Evidemment : :resolu:

Bonjour,

Après un nuit de sommeil, les idées sont plus claires.

Il peut y avoir un souci pour le dernier commentaire, si tu enregistres plusieurs achats dans la même journée.

La première solution est d'enregistrer la date d'achats avec l'heure exacte.

La seconde, si tu as des index auto-incrémentés, la dernière écriture sera celle ayant le n° d'index le plus élevé. Dans ce cas, tu modifies la requête ainsi :
Code:

1 2 3 4 5 SELECT C.CLI_ID, CLI_Nom, CLI_Prenom, ACH_Commentaire, ACH_DATE FROM T_CLIENTS C LEFT JOIN T_ACHATS A ON C.CLI_ID = A.ACH_Id_Client WHERE ACH_Commentaire IS NULL OR ACH_ID = (SELECT MAX(ACH_ID) FROM T_ACHATS WHERE ACH_Id_Client = C.CLI_ID) ORDER BY CLI_Nom, CLI_Prenom
Bon courage dans ton développement

15/02/2012, 01h15
archonte

Citation:

Envoyé par seabs

...
La seconde, si tu as des index auto-incrémentés, la dernière écriture sera celle ayant le n° d'index le plus élevé.
...
Bon courage dans ton développement

Effectivement, j'ai fini par avoir cette idée plus simple que de travailler sur des dates après avoir posté ma réponse hier soir ! :chin: MERCI de t'être re-pencher sur mon cas :ccool:A force de :java: le code s'améliore progressivement !