How Get Values from tables - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

How Get Values from tables

Author

Topic

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 07:25:01

Hi,
I am using four tables.Following are the tables and values.
forumcategorymgmt
CatId CatName

1 Asp.net
2 C#
3 VB
forumrequest
FreqId CatId UserId title PostDate

1 1 1 ASp.net Content 2009-12-10
2 1 1 Developement content 2009-12-11
3 2 2 C# Content 2009-12-12

forumreplies
FRepId FReqId

1 1
2 1
3 1
4 2

UserMgmt

UserId Name

1 Raj
2 Ram
UserId Name

I need query for following values

CatName Title UserName PostDate Topics Posts

Asp.net Developement content Raj 2009-12-11 2 3
C# C# Content Ram 2009-12-12 1 1
VB null null null 0 0

Here Title is the recent post in the category.
Here Topic is the no of post per category.
Here Posts is the total response per post.

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 07:54:09

[code]SELECT fcm.CatName,fr.Title,um.Name AS UserName,fr.PostDate,fr1.freqCnt AS Topics,frep.Replies AS Posts
FROM forumcategorymgmt fcm
INNER JOIN forumrequest fr
ON fr.CatId=fcm.CatId
INNER JOIN (SELECT CatId,MAX(freqId) AS Latest,COUNT(FreqId) AS FreqCnt
FROM forumrequest
GROUP BY CatId)fr1
ON fr1.CatId=fr.CatId
AND fr1.Latest=fr.freqId
INNER JOIN UserMgmnt um
ON um.UserId=fr.UserId
INNER JOIN (SELECT FreqId,COUNT(FRepID) AS Replies
FROM forumreplies
GROUP BY FreqId)frep
ON frep.FreqId=fr.FreqId
[/code]

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 08:05:34

Hi,
Thanks for your reply.
I am trying to use this query but i don't have any values.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:09:57

what do you mean you dont have any values?

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 08:12:15

Hi,
Thanks for your reply.

It display blank values.It doesnot return any row.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:25:14

what about this?


SELECT fcm.CatName,fr.Title,um.Name AS UserName,fr.PostDate,ISNULL(fr1.freqCnt,0) AS Topics,ISNULL(frep.Replies,0) AS Posts
FROM forumcategorymgmt fcm
LEFT OUTER JOIN forumrequest fr
ON fr.CatId=fcm.CatId
LEFT OUTER JOIN (SELECT CatId,MAX(freqId) AS Latest,COUNT(FreqId) AS FreqCnt
            FROM forumrequest
            GROUP BY CatId)fr1
ON fr1.CatId=fr.CatId
AND fr1.Latest=fr.freqId
LEFT OUTER JOIN UserMgmnt um
ON um.UserId=fr.UserId
LEFT OUTER JOIN (SELECT FreqId,COUNT(FRepID) AS Replies
            FROM forumreplies
            GROUP BY FreqId)frep
ON frep.FreqId=fr.FreqId

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 08:41:12

Hi,
Thanks for your reply.

I got following values.

CatName Title UserName PostDate Topics Posts
Asp.net ASp.net Content Raj 2009-12-10 0 3
Asp.net Developement content Raj 2009-12-11 2 1
C# C# Content Ram 2009-12-12 1 0
VB null null null 0 0

But I need following values

CatName Title UserName PostDate Topics Posts

Asp.net Developement content Raj 2009-12-11 2 3
C# C# Content Ram 2009-12-12 1 1
VB null null null 0 0

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 08:59:47

[code]
SELECT fcm.CatName,tmp.Title,um.Name AS UserName,tmp.PostDate, tmp.Topics,ISNULL(frep.Replies,0) AS Posts
FROM forumcategorymgmt fcm
LEFT OUTER JOIN
(SELECT fr.FreqId,
fr.UserId,
fr.CatId,
fr.Title,
fr.PostDate,
ISNULL(fr1.freqCnt,0) AS Topics
FROM forumrequest fr
INNER JOIN (SELECT CatId,MAX(freqId) AS Latest,COUNT(FreqId) AS FreqCnt
FROM forumrequest
GROUP BY CatId)fr1
ON fr1.CatId=fr.CatId
AND fr1.Latest=fr.freqId
)tmp
ON tmp.CatId=fcm.CatId
LEFT OUTER JOIN UserMgmnt um
ON um.UserId=tmp.UserId
LEFT OUTER JOIN (SELECT FreqId,COUNT(FRepID) AS Replies
FROM forumreplies
GROUP BY FreqId)frep
ON frep.FreqId=tmp.FreqId
[/code]

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 09:09:21

Hi,
Thanks for your reply.

I got following values.

CatName Title UserName PostDate Topics Posts
Asp.net Developement content Raj 2009-12-11 2 0
C# C# Content Ram 2009-12-12 1 0
VB null null null 0 0

But I need following values

CatName Title UserName PostDate Topics Posts

Asp.net Developement content Raj 2009-12-11 2 3
C# C# Content Ram 2009-12-12 1 1
VB null null null 0 0

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 09:11:27

can you explain how you get 3 & 1?

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-02 : 09:23:03

Hi,
Thanks for your reply.

From forumreplies table
FRepId FReqId

1 1
2 1
3 1
4 2

Here FreqId 1 is the newpost for Asp.net Category.
Here FreqId 2 is the newpost for C# Category

First Category Asp.net contains three replies and
Secaond Category C# contains one replies
I got following values.

So I need to display Topics and Posts.
Topics is the no of newpost for each category.
Posts is the no of replies for each category.

So I need following values

CatName Title UserName PostDate Topics Posts

Asp.net Developement content Raj 2009-12-11 2 3
C# C# Content Ram 2009-12-12 1 1
VB null null null 0 0

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 09:47:22

nope...as per wat you posted newpost for asp.net category(CatId 1) is FreqId 2 made on 2009-12-11 and its replies are only 1 with FrepId 4

karthikMCA
Starting Member

9 Posts

Posted - 2010-01-03 : 23:35:00

Sorry,
A small mistake on forumreplies table.

forumcategorymgmt table
CatId CatName

1 Asp.net
2 C#
3 VB
forumrequest table

FreqId CatId UserId title PostDate

1 1 1 ASp.net Content 2009-12-10
2 1 1 Developement content 2009-12-11
3 2 2 C# Content 2009-12-12

forumreplies table
FRepId FReqId

1 1
2 1
3 2
4 3

UserMgmt

UserId Name

1 Raj
2 Ram

I need query for following values

CatName Title UserName PostDate Topics Posts

Asp.net Developement content Raj 2009-12-11 2 3
C# C# Content Ram 2009-12-12 1 1
VB null null null 0 0

Here Title is the recent post in the category.
Here Topic is the no of post per category.
Here Posts is the total response per post.

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 02:18:32

[code]SELECT fcm.CatName,tmp.Title,um.Name AS UserName,tmp.PostDate, ISNULL(tmp.Topics,0) AS Topics,tmp.Posts
FROM forumcategorymgmt fcm
LEFT OUTER JOIN
(SELECT fr.FreqId,
fr.UserId,
fr.CatId,
fr.Title,
fr.PostDate,
fr1.freqCnt AS Topics,
fr1.Posts
FROM forumrequest fr
INNER JOIN (SELECT CatId,MAX(freqId) AS Latest,COUNT(DISTINCT FreqId) AS FreqCnt,COUNT(FRepId) AS Posts
FROM forumrequest fr
LEFT OUTER JOIN forumreplies frp
ON frp.FreqId =fr.FreqId
GROUP BY CatId)fr1
ON fr1.CatId=fr.CatId
AND fr1.Latest=fr.freqId
)tmp
ON tmp.CatId=fcm.CatId
LEFT OUTER JOIN UserMgmnt um
ON um.UserId=tmp.UserId
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources