join query - 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

General SQL Server Forums

New to SQL Server Programming

join query

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-10-31 : 03:20:19

Hi Friends,

I have five table
Kr_Category
CatId
Name
Parentid
CreationDateTime
ModifiedDateTime

Kr_Request
ReqId
CatId
UserId
Topic
Description
CreationDateTime
ModifiedDateTime

Kr_RequestReply
TopId
ReqId
UserId
Topic
Description
CreationDateTime
ModifiedDateTime

Kr_Usermanagement
UserId
Name
CreationDateTime
ModifiedDateTime

This is my query only for Category not subcategory

select r.*,(Select Count(*) From KR_Request r1 join KR_RequestReply rr ON r1.ReqId=rr.ReqId where r1.Deleted=0 and r1.CatId=r.CatId and r1.ReqId=r.ReqId) as Replycounts,(Select Top 1 u.Name From KR_Request as r2 inner join KR_RequestReply as rr1 inner join KR_UserManagement as u ON rr1.UserId=u.UserId ON r2.ReqId=r.ReqId where rr1.UserId=u.UserId and r2.ReqId=rr1.ReqId and r2.Deleted=0 and r2.CatId=r.CatId and rr1.CreationDateTime <= (getdate()) order by rr1.CreationDateTime desc) as Lastpost,(Select Top 1 rr1.CreationDateTime From KR_Request as r2 inner join KR_RequestReply as rr1 inner join KR_UserManagement as u ON rr1.UserId=u.UserId ON r2.ReqId=r.ReqId where rr1.UserId=u.UserId and r2.ReqId=rr1.ReqId and r2.Deleted=0 and r2.CatId=r.CatId and rr1.CreationDateTime <= (getdate()) order by rr1.CreationDateTime desc) as Lastdate from KR_Request as r where r.Deleted=0 and r.Status=1 order by r.ReqId desc

I have display the Record for

Category and Subcategory via

[Request List],[Status],[noofReplies],[Last Reply]

Thanks and Regards
E.sambath kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 03:37:20

are you using sql 2005?