Query Wanted.

irfan4 · July 15th, 2003, 04:49 AM

Hi Guys.

i have three tables

tblGroups [groupid, groupname, description]
tbltopics [topicid, topicname, description]
tblmessage[messageid, topicid, subject, body, date]

As you see i am designing a discussion forum... i want to display just like you see the first screen of p2p forums of this site...

[group name ] [group desc.] [total topics] [total replies]
group1 this is g1 100 100

i hope u ppl understand...

i want the query...'

thanks.
waiting
Irfan Bashir(pk)

MRvLuijpen · July 15th, 2003, 06:49 AM

Hello Irfan,

I hope I understand correctly, and have thought of the following code

Code:

 SELECT [tblGroups].[groupname], [tblGroups].[description], Count([tblTopics].[Topicname]) AS [Total Topics], Count([tblMessage].[messageid]) AS [total replies]
FROM tblTopics INNER JOIN (tblGroups INNER JOIN tblMessage ON [tblGroups].[groupid]=[tblMessage].[groupid]) ON [tblTopics].[Topicid]=[tblMessage].[topicid]
GROUP BY [tblGroups].[groupname], [tblGroups].[description];

NB: the tblMessage is extended with the field groupid. perhaps this field has to go into the tbltopic, and then the code would be something like

Code:

 SELECT tblGroups.groupname, tblGroups.description, Count(tblTopic2.Topicid) AS [total topics], Count(tblMessage2.messageid) AS [total replies]
FROM (tblGroups INNER JOIN tblTopic2 ON tblGroups.groupid = tblTopic2.GroupId) INNER JOIN tblMessage2 ON tblTopic2.Topicid = tblMessage2.topicid
GROUP BY tblGroups.groupname, tblGroups.description;

Hope this helps

Marc