Difficulté à construire une requête

**coolcpu** · 06/10/2003, 05h04

Je suis actuellement à la construction d'un forum de discussion pour un projet éducationnel en base de données. Mon expérience limité avec le langage SQL ne me permet pas, même après plusieurs heures de lecture, de construire correctement une requête qui génèrera le résultat souhaité. C'est pourquoi je sollicite votre aide.

Problème :

Je dois obtenir les éléments suivants :

1. Le nom du Forum et sa description (Table Forum)
2. Le nombre de Thread pour chaque forum (COUNT() depuis la table Thread)
3. Le nombre de Message pour chaque forum (pas d'un seul thread mais le total des messages pour un forum) (COUNT() depuis la table Message)
4. Affichage du nom de celui qui a posté le dernier message de ce forum ainsi que la date du message (depuis la table Message et Membre)

J'utilise Microsoft SQL Server version 7.0

J'ai bien tenté de construire des requêtes mais sans succès :

Code :

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

1
2
3
4
5
6
 
SELECT COUNT(*) AS nb_Messages_par_Forum, F.forumId 
FROM Forum F
	JOIN Thread T ON T.forumId = F.forumId
	JOIN Message M ON T.threadId = M.threadId
GROUP BY F.forumId

Cette requête retourne bien le résultat du nombre de messages par forum mais n'inclus pas les forums ne contenant pas de messages.

Code :

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

1
2
3
4
5
6
 
SELECT COUNT( * ) AS Total_Thread, F.forumId, F.forumName, F.forumDescription, F.forumCreationDate,F.forumStickyBit
FROM Forum F
	LEFT OUTER JOIN Thread T ON F.forumId = T.forumId
GROUP BY F.forumId, F.forumName, F.forumDescription, F.forumCreationDate,
					F.forumStickyBit

Cette dernière retourne un peu plus d'information mais toujours loin du résultat souhaité ...

Ce sont les deux seuls résultats que j'ai pu obtenir qui se rapprochent le plus de ce que je cherche. Le but ultime est bien sûr de faire le travail en une seule requête (si possible) car de cette manière elle sera plus performante et sera plus simple à implanter dans le code des pages web.

Sous forme de tableau :

Code :

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

1
2
3
4
5
6
7
 
forumId forumName       forumDescription   nbr_Thread nbr_messages messageDate memberLastName memberfirstName
------- ---------       ----------------   ---------- ------------ ----------- -------------- ---------------
   1    Ben cé le pre.. Voila notre pre...      2           4      2003-10-25   Thomson        Robert
   2    La le deuxième  Description deu...      1           1      2003-11-05   Lavoie         George
   3    le troisième    Description tro...      0           0            null   null           null
   4    le quatrième    Description qua...      0           0            null   null           null

Je vous remerci à l'avance pour votre aide.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
 
/* Scripts de création des tables */
 
CREATE TABLE [dbo].[Forum] (
	[forumId] [int] IDENTITY (1, 1) NOT NULL ,
	[memberId] [int] NULL ,
	[forumName] [varchar] (200) NULL ,
	[forumDescription] [varchar] (200) NULL ,
	[forumCreationDate] [char] (10) NULL ,
	[forumStickyBit] [int] NULL ,
	[forumStatus] [int] NULL 
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[Member] (
	[memberId] [int] IDENTITY (1, 1) NOT NULL ,
	[memberLogin] [varchar] (30) NULL ,
	[memberPasswd] [varchar] (50) NULL ,
	[memberLastName] [varchar] (30) NULL ,
	[memberFirstName] [varchar] (30) NULL ,
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[Message] (
	[messageId] [int] IDENTITY (1, 1) NOT NULL ,
	[threadId] [int] NULL ,
	[memberId] [int] NULL ,
	[messageTopic] [varchar] (200) NULL ,
	[messageBody] [text] NULL ,
	[messageCreationDate] [char] (10) NULL ,
	[messageStatus] [int] NULL ,
	[messageStickyBit] [int] NULL 
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[Thread] (
	[threadId] [int] IDENTITY (1, 1) NOT NULL ,
	[forumId] [int] NULL ,
	[memberId] [int] NULL ,
	[threadTopic] [varchar] (200) NULL ,
	[threadDesc] [text] NULL ,
	[threadCreationDate] [char] (10) NULL ,
	[threadStatus] [int] NULL ,
	[threadStickyBit] [int] NULL 
) ON [PRIMARY]
 
/* Données de la table forum */
 
INSERT INTO Forum (memberId,forumName,forumDescription,forumCreationDate,forumStickyBit,forumStatus)
VALUES (1,'Ben cé le premier','Voila notre premier Forum','2003-09-08',0,1);
INSERT INTO Forum (memberId,forumName,forumDescription,forumCreationDate,forumStickyBit,forumStatus)
VALUES (2,'La le deuxième','Description deuxième forum','2003-09-08',0,1);
INSERT INTO Forum (memberId,forumName,forumDescription,forumCreationDate,forumStickyBit,forumStatus)
VALUES (1,'le troisième','Description troisième forum','2003-10-04',0,1);
INSERT INTO Forum (memberId,forumName,forumDescription,forumCreationDate,forumStickyBit,forumStatus)
VALUES (1,'le quatrième','Description quatrième forum','2003-10-05',0,1);
 
/* Données de la table member */
 
INSERT INTO member (memberLogin,memberPasswd,memberLastName,memberFirstName)
VALUES ('Login1','passwd1','Thomson','Robert');
INSERT INTO member (memberLogin,memberPasswd,memberLastName,memberFirstName)
VALUES ('Login2','passwd2','Lavoie','George');
 
/* Données de la table thread */
 
INSERT INTO thread (forumId,memberId,threadTopic,threadDesc,threadCreationDate,threadStatus,threadStickyBit)
VALUES (1,1,'Les HD','','2003-09-08',1,0);
INSERT INTO thread (forumId,memberId,threadTopic,threadDesc,threadCreationDate,threadStatus,threadStickyBit)
VALUES (1,2,'Les Carte Vidéo','','2003-10-08',1,0);
INSERT INTO thread (forumId,memberId,threadTopic,threadDesc,threadCreationDate,threadStatus,threadStickyBit)
VALUES (2,1,'Les CD-Rom','','2003-11-08',1,0);
 
/* Données de la table message */
 
INSERT INTO message (threadId,memberId,messageTopic,messageBody,messageCreationDate,messageStatus,messageStickyBit)
VALUES (1,1,'Maxtor 40Go','Cé les pires','2003-09-08',1,0);
INSERT INTO message (threadId,memberId,messageTopic,messageBody,messageCreationDate,messageStatus,messageStickyBit)
VALUES (2,1,'HD 20Go','','2003-10-21',1,0);
INSERT INTO message (threadId,memberId,messageTopic,messageBody,messageCreationDate,messageStatus,messageStickyBit)
VALUES (3,2,'HD 60 Go','','2003-11-05',1,0);
INSERT INTO message (threadId,memberId,messageTopic,messageBody,messageCreationDate,messageStatus,messageStickyBit)
VALUES (2,1,'CD-rom','','2003-10-25',1,0);
INSERT INTO message (threadId,memberId,messageTopic,messageBody,messageCreationDate,messageStatus,messageStickyBit)
VALUES (1,2,'Carte Vidéo','','2003-06-17',1,0);

**vttman** · 06/10/2003, 09h57

Essaye cela ...

Modif COUNT( t.forumId ) à la place COUNT( * )

Code :

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

1
2
3
4
5
SELECT COUNT( t.forumId ) AS Total_Thread, F.forumId, F.forumName, F.forumDescription, F.forumCreationDate,F.forumStickyBit 
FROM Forum F 
   LEFT OUTER JOIN Thread T ON F.forumId = T.forumId 
GROUP BY F.forumId, F.forumName, F.forumDescription, F.forumCreationDate, 
               F.forumStickyBit

**coolcpu** · 07/10/2003, 00h39

Merci vttman ca répond à une de mes questions... Maintenant les calculs des statistiques sont justes même pour les forums n'ayant pas de threas et/ou messages.

Je vais vous soumettre ce que j'ai de fait jusqu'à maintenant car j'ai fait un peu de progrès ...

Cette requête retourne maintenant une partie de l'information mais je n'ai pas réussi à trouver la requête me permettant d'ajouter la date du dernier message pour un Forum ainsi que le nom de l'utilisateur qui l'a créé.

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
 
SELECT   F.forumId, F.forumName, F.forumDescription, B.nb_Threads,A.nb_Messages  
FROM Forum F,
 
	(SELECT COUNT(M.threadId) AS nb_Messages, F.forumId AS A_forumId
		FROM Forum F 
			LEFT OUTER JOIN Thread T ON F.forumId = T.forumId 
			LEFT OUTER JOIN Message M ON T.threadId = M.threadId
		GROUP BY F.forumId)A,
 
	(SELECT COUNT(t.forumId) nb_Threads, F.forumId AS B_ForumId
		FROM Forum F
			LEFT OUTER JOIN Thread T ON F.forumId = T.forumId
		GROUP BY F.forumId)B
 
WHERE F.forumId = A_forumId 
AND A_forumId = B_forumId
 
GROUP BY A.nb_Messages , B.nb_Threads, F.forumId, F.forumName, F.forumDescription
 
ORDER BY F.forumId ASC

J'ai réussi à faire un petit bout de la requête qui retourne le memberId du dernier message pour chaque forum, mais je n'ai pas pu aller plus loin.

Code :

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

1
2
3
4
5
6
 
(SELECT MAX(M.messageId) as MAXI, F.forumId
	FROM Forum F 
		LEFT OUTER JOIN Thread T ON F.forumId = T.forumId
		LEFT OUTER JOIN Message M ON T.threadId = M.ThreadId
	GROUP BY F.forumId)

Un coup de main serait apprécié ...

**vttman** · 07/10/2003, 08h56

Tu peux essayer de passer par des sous-requêtes
pour ce genre d'interrogation ou passer par des vues
ce qui te permet d'avoir des requêtes plus claires ...

ex de vue
=>

Code :

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

1
2
3
4
5
6
7
8
9
create view msg_id_max_forum
as
SELECT MAX(M.messageId) as MAXI_id, F.forumId 
   FROM Forum F 
      LEFT OUTER JOIN Thread T ON F.forumId = T.forumId 
      LEFT OUTER JOIN Message M ON T.threadId = M.ThreadId 
   GROUP BY F.forumId
 
GO

Tu peux t'inspirer de cette requête pour résoudre
ton problème ...

Code :

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

1
2
3
4
5
6
SELECT m.messageCreationDate, F.forumId ,M.MessageId
   FROM forum f 
      LEFT OUTER JOIN Thread T ON F.forumId = T.forumId 
      LEFT OUTER JOIN Message M ON T.threadId = M.ThreadId 
      inner join  msg_id_max_forum MF  
                 on F.forumId = mF.forumId and mf.maxi_id = m.messageid

**SQLpro** · 07/10/2003, 14h02

La gestion d'un forum doit être modélisé par un arbre :
1 question => 1 thread ==> un arbre de réponse.
Pour modéliser un arbre sans avoir à utiliser de procédure récursives, lire :
http://sqlpro.developpez.com/Tree/SQL_tree.html

A +

**SQLpro** · 07/10/2003, 14h03

Tiens j'ai oublié ça...
Dans mon bouquin, il y a un exemple de gestion de forum p 459 à 463.

A +

**coolcpu** · 08/10/2003, 00h43

Voila j'ai réussi avec l'aide de vttman à construire l'ensemble de ma requête et je l'en remerci. Le truc des vues m'a permis de comprendre plus facilement la structure de ma requête plus complexe. Je suis surpris de la rapidité des réponses de ce Forum et je vais sûrement my référer dans le futur ... SQLpro je vais surement étudier la question un peu plus tard. La méthode que j'utilise n'est surement pas la meilleure mais avec un peu d'expérience je vais sûrement y arriver.

Voila l'ensemble de ma requête :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
 
SELECT   F.forumId, F.forumName, F.forumDescription, F.forumStickyBit, B.nb_Threads, A.nb_Messages,
		C.messageCreationDate,C.memberLogin
	FROM Forum F,
	(SELECT COUNT(M.threadId) AS nb_Messages, F.forumId AS A_forumId
		FROM Forum F 
			LEFT OUTER JOIN Thread T 
				ON F.forumId = T.forumId 
			LEFT OUTER JOIN Message M 
				ON T.threadId = M.threadId
		GROUP BY F.forumId)A,
 
	(SELECT COUNT(t.forumId) nb_Threads, F.forumId AS B_ForumId
		FROM Forum F
			LEFT OUTER JOIN Thread T 
				ON F.forumId = T.forumId
		GROUP BY F.forumId)B,
	(SELECT F.forumId AS C_forumId, LM.messageCreationDate, LM.memberLogin
		FROM Forum F
			LEFT OUTER JOIN (SELECT M.messageCreationDate, LM.forumId, MB.memberLogin
							FROM Message M
								JOIN (SELECT M.messageCreationDate, F.forumId ,M.messageId
   									FROM forum F
      								LEFT OUTER JOIN Thread T 
										ON F.forumId = T.forumId
      								LEFT OUTER JOIN Message M 
										ON T.threadId = M.ThreadId
      								INNER JOIN  (SELECT MAX(M.messageId) as MAXI_id, F.forumId
   												FROM Forum F
      											LEFT OUTER JOIN Thread T 
													ON F.forumId = T.forumId
      											LEFT OUTER JOIN Message M 
													ON T.threadId = M.ThreadId
   												GROUP BY F.forumId) MF 
										ON F.forumId = MF.forumId 
										AND MF.maxi_id = M.messageid) LM
								ON M.messageId = LM.messageId
								JOIN Member MB
									ON M.memberId = MB.memberId) LM
				ON LM.forumId = F.forumId)C
 
WHERE F.forumId = A_forumId 
AND A_forumId = B_forumId
AND B_forumId = C_forumId
 
GROUP BY A.nb_Messages , B.nb_Threads, F.forumId, F.forumName, F.forumDescription, F.forumStickyBit, C.messageCreationDate,C.memberLogin

Difficulté à construire une requête

Langage SQL

Discussions similaires

Partager

Partager