Discussion :

[guitoux1]

Bonjour,

Voilà, je me retrouve face à une requête trop complexe pour moi, j'ai besoin d'aide

Contexte :
Il s'agit d'une table qui liste des mails envoyés concernant un article. Elle contient un identifiant unique (de type entier auto-incrémenté), l'identifiant de l'article, l'adresse email du destinataire, un sujet, un corps, une date d'envoi. Cette table est liée à 3 autres tables qui permettent de savoir si le mail est en cours d'envoi, envoyé ou envoi échoué (tables MAILS_EN_COURS, MAILS_OK et MAILS_KO
Je veux afficher un résumé par article, avec le nombre de destinataires total, le nombre de mails envoyés, le nombre en cours d'envoi et le nombre en échec.
Jusque là, pas de problème. Cependant, ma seconde table concernant l'état des mails doit être vidée régulièrement. Je perd alors l'état des mails. Je veux donc mettre à jour un champ ENVOI dans ma première table lors de la purge de la table 2 mettre a NULL le champ IDMAIL de la table 1 qui fait référence à la table 2

Problème :
Comment récupérer dans une seule requête les données issues des tables A + B et les données issues uniquement de la table A ?

Code :

Code :

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

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
TABLE 1 : MAILS_ENVOIS
------------------------
ID | ID_ARTICLE | DESTINATAIRE   | ENVOI | IDMAIL
-------------------------------------------------
1  | 1          | tada1@tutu.com | NULL  | 1
2  | 1          | tudu1@tada.com | NULL  | 2
3  | 1          | tudu2@tada.com | NULL  | 3
4  | 1          | tudu3@tada.com | NULL  | 4
5  | 2          | toto1@tudu.com | NULL  | NULL    -- En cours d'envoi
6  | 2          | toto2@tudu.com | 0     | NULL         -- Echec
7  | 2          | toto3@tudu.com | 1     | NULL         -- Envoyé
 
 
TABLE 2 : MAILS_ENCOURS
--------------------------
ID
--
1    
 
TABLE 3 : MAILS_OK
--------------------
ID
--
2
3
 
TABLE 4 : MAILS_KO
--------------------
ID
--
4

Je veux donc retrouver quelque chose comme ça :

Code :

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

1
2
3
4
5
 
ID_ARTICLE | NB_DESTINATAIRES | NB_OK | NB_EN_COURS | NB_KO
-----------------------------------------------------------------
1          | 4                | 2     | 1           | 1
2          | 3                | 1     | 1           | 1

J'ai bien la requête pour les données présentent dans les tables 1 et 2 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT
	MAILS_ENVOIS.ID_ARTICLE, 
	COUNT(*) AS NB_DESTINATAIRES,
	COUNT(M1.ID) AS NB_OK,
	COUNT(M2.ID) AS NB_EN_COURS,
	COUNT(M3.ID) AS NB_KO,
FROM         
    dbo.MAILS_ENVOIS 
    LEFT JOIN MAILS_EN_COURS M1 ON M1.ID = MAILS_ENVOIS.IDMAIL
    LEFT JOIN MAILS_OK M2 ON M2.ID = MAILS_ENVOIS.IDMAIL
    LEFT JOIN MAILS_KO M3 ON M3.ID = MAILS_ENVOIS.IDMAIL
WHERE
	MAILS_ENVOIS.IDMAIL IS NOT NULL
GROUP BY 
	MAILS_ENVOIS.ID_ARTICLE

Je voudrais savoir comment modifier cette requête pour qu'elle prenne en compte les cas ou IDMAIL est NULL et que l'état des mails ne se trouve plus via une jointure mais via le champ ENVOI (NULL = en cours, 1 = envoyé et 0 = échec) (Dans ce cas, IDMAIL est NULL).

Merci de votre aide

[SQLpro]

On ne peut pas comprendre votre logique ni comment vous comptez ces éléments. A quoi servent les tables MAILS_OK, MAILS_KO et ENCOURS ?
Comment sont elles liées à la table MAIL_ENVOI ????

Sans ces éléments de sémantique il est impossible de vous aider !

A +

[guitoux1]

Désolé, c'était pas assez clair.
Pour répondre à vos questions :
Table MAILS_OK : la liste des mails qui sont effectivement partis à leur destinataire
Table MAILS_KO : la liste des mails dont l'envoi a échoué
Table MAILS_EN_COURS : la liste de mails en fil d'attente (en attente d'être envoyé, ensuite, ils vont soit dans MAILS_OK soit dans MAILS_KO)
Et chacune de ces table est liée par sont identifiant que l'on retrouve alors dans la table MAILS_ENVOIS dans la colonne IDMAIL

Est-ce plus clair ainsi ?

[CinePhil]

N'aurait-il pas été plus simple d'ajouter une colonne Etat à la table des mails ?
Si c'est encore possible, je vous recommande de revoir la conception de votre BDD.

[guitoux1]

ben en fait je ne peux pas faire grand chose. J'utilise la fonctionnalité Database Mail de SQL Server 2005. Ca me permet donc d'envoyer des mails via mon serveur SQL. J'appel pour cela une procédure stockée avec les paramètres du mail (sujet, body, destinataire, ...) et la procstock me renvoi un identifiant correspondant à l'enregistrement créé dans une table système. L'envoi de mails est un processus asynchrone. Il sont mis en file d'attente avant envoi.
Je stock cet identifiant dans ma table MAILS_ENVOIS dans la colonne IDMAIL. Je peux ensuite consulter les mails envoyés par SQL Server en requêtant dans 3 vues disponibles qui sont ici MAILS_OK, MAILS_KO et MAILS_EN_COURS.
Le problème et que la table système où SQL Server stock les mails va très vite montée en taille. Je veux alors nettoyer cette table régulièrement (via une procstock système dispo sur SQL Server) sans pour autant perdre les infos des mails envoyés ou non. C'est pour cela que quand je viderais cette table système, j'updaterais ma table MAILS_ENVOIS en mettant alors à NULL la colonne IDMAIL (devenu inutile) et mettre à jour la colonne ENVOI avec soit NULL, 0 ou 1 selon l'état du mail dans la table système.

Et surtout, je voudrais faire ça en une seule requête pour ne pas avoir à me soucier de savoir si quand je lis ma table MAILS_ENVOIS, un enregistrement correspondant existe dans la table système (les 3 vues) ou pas.