query for no of topics & posts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query for no of topics & posts

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 04:14:30

I need query to fetch values of postedname,repliername,categoryname,no of topics,no of posts from the 4 tables mentioned below.

tablename----categorymgmt
fields----categoryid,categoryname,categorydesc

tablename----forum_topics
fields----topicid,categoryid,topic,userid,description

tablename----from forum_reply
fileds----replyid,topicid,userid

tablename----usermgmt
fileds----userid,username

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 04:30:25

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 04:36:24

Hi visakh,

i m getting error "invalid userid,topicid"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 04:49:10

quote:
Originally posted by vidhya

Hi visakh,

i m getting error "invalid userid,topicid"

dont you have those columns in your table?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 05:16:13

the query is working.It displays like this
Vidhyashri shri Engineering 1 1
Vidhyashri Nisha Engineering 1 1
But i need to display by category wise and display the postedname who posted recently for this category and repliername who replied recently for this category and display no of topics posted for this category and display no of replies who replied for this category.

vidhyashri nisha Engineering 1 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 05:23:58

quote:
Originally posted by vidhya

the query is working.It displays like this
Vidhyashri shri Engineering 1 1
Vidhyashri Nisha Engineering 1 1
But i need to display by category wise and display the postedname who posted recently for this category and repliername who replied recently for this category and display no of topics posted for this category and display no of replies who replied for this category.

vidhyashri nisha Engineering 1 2

do you mean recently posted article and most recent reply for it?
or recent post and last reply but not necessarily for this post?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 05:27:45

recent post and last reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 05:38:34

is this what you want?

select um1.username as postedname,
um2.username as repliername,
cm.categoryname,
cc.catcount,
uc.usrcount
from forum_topics ft
inner join categorymgmt cm
on cm.categoryid=ft.categoryid
inner join forum_reply fr
on fr.topicid=ft.topicid
inner join (select ft1.categoryid,max(ft1.topicid) as recenttopic,max(fr1.replyid)as recentreplyid
from forum_topics ft1
inner join forum_reply fr1
ON fr1.topicid=ft1.topicid
GROUP BY ft1.categoryid) maxon max.categoryid=cm.categoryid
and max.recenttopic=ft.topicid
and max.replyid=fr.replyid
inner join usermgmt um1
on um1.userid=ft.userid
inner join usermgmt um2
on um2.userid=fr.userid
inner join (select categoryid,count(*) as catcount from forum_topics 
            group by categoryid)cc
on cc.categoryid=cm.categoryid
inner join (select userid,count(*) as usrcount from forum_topics 
            group by userid)uc
on uc.userid=ft.userid

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 05:43:53

It shows an error Incorrect syntax near max,cc,uc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 05:51:34

[code]select um1.username as postedname,
um2.username as repliername,
cm.categoryname,
cc.catcount,
uc.usrcount
from forum_topics ft
inner join categorymgmt cm
on cm.categoryid=ft.categoryid
inner join forum_reply fr
on fr.topicid=ft.topicid
inner join (select ft1.categoryid,max(ft1.topicid) as recenttopic,max(fr1.replyid)as recentreplyid
from forum_topics ft1
inner join forum_reply fr1
ON fr1.topicid=ft1.topicid
GROUP BY ft1.categoryid) max
on max.categoryid=cm.categoryid
and max.recenttopic=ft.topicid
and max.replyid=fr.replyid
inner join usermgmt um1
on um1.userid=ft.userid
inner join usermgmt um2
on um2.userid=fr.userid
inner join (select categoryid,count(*) as catcount from forum_topics
group by categoryid)cc
on cc.categoryid=cm.categoryid
inner join (select userid,count(*) as usrcount from forum_topics
group by userid)uc
on uc.userid=ft.userid[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 06:08:26

it shows error Invalid column replyid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 09:32:27

Make sure the column exists in both the tables forum_topics and forum_reply

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 09:37:26

[code]select um1.username as postedname,
um2.username as repliername,
cm.categoryname,
cc.catcount,
uc.usrcount
from forum_topics ft
inner join categorymgmt cm
on cm.categoryid=ft.categoryid
inner join forum_reply fr
on fr.topicid=ft.topicid
inner join (select ft1.categoryid,max(ft1.topicid) as recenttopic,max(fr1.replyid)as recentreplyid
from forum_topics ft1
inner join forum_reply fr1
ON fr1.topicid=ft1.topicid
GROUP BY ft1.categoryid) max
on max.categoryid=cm.categoryid
and max.recenttopic=ft.topicid
and max.recentreplyid=fr.replyid
inner join usermgmt um1
on um1.userid=ft.userid
inner join usermgmt um2
on um2.userid=fr.userid
inner join (select categoryid,count(*) as catcount from forum_topics
group by categoryid)cc
on cc.categoryid=cm.categoryid
inner join (select userid,count(*) as usrcount from forum_topics
group by userid)uc
on uc.userid=ft.userid[/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-12 : 10:36:58

hi visakh,

I m getting No.Of posts as 1. it should be 2.

postedname,repliername,categoryname,no of topics,no of posts
Vidhyashri shri Engineering 1 1
Vidhyashri Nisha Engineering 1 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 12:54:07

Can you please provide some sample data from your tables? That should make it more clear

vidhya
Posting Yak Master

108 Posts

Posted - 2008-06-13 : 09:43:13

you copy these data and paste in xls sheet. you can clearly get the field name and values for that.

Forum_topic
forumtopic_id categoryid forum_topic UserId Forum_description Status Deleted CreationDateTime ModifiedDateTime
1 1 Software Engineering 25 sample description about software engineering 0 0 6/12/2008 6/12/2008 0:00

Forum_Reply
replyid ForumTopic_id UserId ForumReply_comments Status Deleted CreationDateTIme ModifiedDateTime
1 1 25 ghghryrty 0 0 6/12/2008 6/12/2008
2 1 26 gertert 0 0 6/12/2008 6/12/2008
3 1 25 sample testing 1 0 6/12/2008 18:44 6/12/2008 18:44

Usermgmt
UserId UserTypeId UserName FirstName LastName Password EMail BusinessName SubjectName CountryId StateId City Address1 Address2 SecurityQuest SecurityAnswer NewsLetterSubscription Status Deleted CreationDateTime ModifiedDateTime
2 4 Dhina Dhina Karan Dhina Dhina@Dhina.com IT Technology computer 1 Chennai First Street Chennai NickName Dina 0 0 0 3/5/2007 3/8/2007
3 4 Karans Naveen Karan Karan dhinaharan.thangaraj@g-antssoft.com SSM Information Technology 1 Bangalore Bangalore MG Road Bangalore PetName ABC 0 0 0 6/7/2007 8/9/2007
9 2 Mr.Prof Rajus RajuUser password senthil.dhaktchana@g-antssoft.com SSM CSE 1 XYZ QWWERTTYY FGFGFHGJHJHJH Your Nick Name? RTRTR 0 0 0 3/1/2008 20:04 3/1/2008 20:04
13 3 shri shri shri shri vidhyashrimca@yahoo.com mani mani 16 22 chennai alwarpet Your Nick Name? sweety 0 0 0 3/15/2008 20:27 3/15/2008 20:27
14 1 shri shri shri shri vidhyashrimca@yahoo.com mani mani 16 22 chennai alwarpet Your Nick Name? sweety 0 0 0 3/15/2008 20:36 3/15/2008 20:36
15 4 vidhya vidhya shri shri vidhyashrimca@yahoo.com 16 15 chennai alwarpet Your Nick Name? sweety 0 0 0 3/17/2008 11:33 3/17/2008 11:33
16 1 Ashok Ashok Muthu great asfasfasfaf 16 26 chennai ashokNagar mainRoad Your First Mobile Number? 1111111111 0 0 0 3/25/2008 12:34 3/25/2008 12:34
17 1 kiran kirankumar raja raja kirankumar.raja@g-antssoft.com 16 26 chennai annanagar Your Nick Name? silence 0 0 0 3/25/2008 13:30 3/25/2008 13:30
18 1 sadeesh sadeesh babu shri gsadeesh@yahoo.co.in shri 44 28 chennai parrys Main Road Your Nick Name? silence 0 0 0 3/27/2008 20:24 3/27/2008 20:24
19 1 satheesh satheesh kumar skumar sdsatheesh@yahoo.com 16 22 chennai asfasfa asdfasd Your Nick Name? silence 0 0 0 3/31/2008 16:59 3/31/2008 16:59
20 1 vidhyashri vidhyashri balasubramanian shribalu a@as.net Mani Matric Hr. Sec. School Computer Science 16 22 chennai 2/44 East Street Thanjavur Your First Vehicle? bicycle 0 0 0 4/3/2008 17:41 4/3/2008 17:41
22 1 vidhyashri vidhya shri risen shri@shri.net Mani Matri Hr.Sec School computer Science 16 22 Thanjavur east street thanjavur Your First Mobile Number? 9.9E+11 0 0 0 4/14/2008 13:25 4/14/2008 13:25
23 2 Ashok Ashok Muthu shri a@asaaa.net Mani Matri Hr.Sec School computer Science 16 22 chennai main road Ashok nagar Your First Mobile Number? 7.77778E+20 0 0 0 4/14/2008 20:12 4/14/2008 20:12
24 3 nisha nisha batcha shri asasa@as.net A.R.R Commerce 16 22 chennai main road thanjavur Your First Mobile Number 2234234 0 0 0 4/15/2008 4/15/2008
25 3 Vidhyashri shri vidhyashri.balasubramanian@g-antssoft.com Risen 16 22 Thanjavur Thanjavur Your Favouriate Place? Thanjavur 0 1 0 5/14/2008 11:41 5/14/2008 11:41
26 2 Nisha NISHA vidhyamca@hotmail.com Mani Matric hr.sec school computer science 16 22 thanjavur thanjavur Your Nick Name? sweety 0 1 0 6/11/2008 20:24 6/11/2008 20:24

CategoryMgmt
CategoryId CategoryName CategoryDesc CategoryType Imageurl Status Deleted CreationDateTime ModifiedDateTime
1 Engineering Computer Science Forum 1 0 2/21/2008 3/12/2008 13:12
2 Engineering Forum 0 0 2/27/2008 11:56 3/12/2008 13:12
3 About Science Forum 1 0 2/29/2008 23:03 2/29/2008 23:03
4 Sample Blog 1 0 2/29/2008 23:04 3/14/2008 21:06
5 SampleTest Blog 1 0 2/29/2008 23:05 3/14/2008 21:06
6 wrt Blog 1 0 3/4/2008 7:18 3/14/2008 21:06
7 sample sample Event /Images/Delete.gif 1 0 3/11/2008 19:17 3/12/2008 13:12
8 sampletesting sampletest Event /Images/evtcalendar.htm 0 0 3/11/2008 19:21 3/11/2008 19:21
9 aaaaaa aaaaaaaaaaa Event /Images/challenge03_05_1024.jpg 0 1 3/11/2008 20:28 3/11/2008 20:28
10 Newspaper artssss Event /Images/bldjp022005_69.jpg 1 0 3/18/2008 16:01 4/30/2008 7:35

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 11:34:03

[code]select m.categoryname,
m.recentposter,
s.recentreplier,
m.topiccount,
s.replycount
from
(select cm.categoryid,
cm.categoryname,
um.username as recentposter,
rec.topiccount
from categorymgmt cm
inner join forum_topics ft
on cm.categoryid=ft.categoryid
inner join (select categoryid,max(forumtopic_id) as recentarticle,count(*) as topiccount
from forum_topics
group by categoryid)rec
on rec.categoryid=ft.categoryid
and rec.recentarticle=ft.forumtopic_id
inner join Usermgmt um
on um.UserId=ft.userid) m
inner join
(select cm1.categoryid,
um1.username as recentreplier,
max.replycount
from categorymgmt cm1
inner join forum_topics ft1
on cm1.categoryid=ft1.categoryid
inner join forum_reply fr
on fr.topicid=ft1.topicid
inner join (select ft2.categoryid,max(fr1.replyid)as recentreply,count(*) as replycount
from forum_topics ft2
inner join forum_reply fr1
ON fr1.topicid=ft2.topicid
GROUP BY ft2.categoryid) max
on max.categoryid=cm1.categoryid
and max.recentreply=fr.replyid
inner join usermgmt um1
on um1.userid=fr.userid)s
on s.categoryid=m.categoryid
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources