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-25 : 06:20:40

Hi friends

I have four table kr_category,kr_reference,kr_referencehistory,Kr_ReferenceRating

kr_category fields

CatId
Name
description
Parentid

kr_reference Fields

RefId
CatId

kr_referencehistory Fields
LinkId
RefId
Title
Url

Kr_ReferenceRating
RatId
RefId
UserId
Points
Status
Deleted
CreationDateTime
ModifiedDateTime

Result

Category Name
SubcategoryName URl Rating NoofRatedMembers
Description

Thanks and Regards
E.sambath kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 07:09:56

Are you both classmates or colleagues?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=113268

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-11-06 : 05:05:54

Yes both are classmates...

I tried the below Query , but it showing the same result 3 times. I mean it showing duplicate row..

select distinct h.title as title,h.description as description,h.url as url,rf.points as points,r.catid as catid,COUNT(r.refid) AS avgrating,r.refid as refid,h.linkid as linkid,Sum(rf.points)/Count(rf.UserId) as Rating,h.status as status,rf.RefId from kr_reference r inner join kr_category c on c.catid=r.catid and r.deleted=0 and r.catid=15 inner join kr_referencehistory h on h.refid=r.refid and h.status=1 inner join kr_referencerating rf on r.refid=rf.refid GROUP BY h.title ,h.description,h.url ,rf.points,r.catid,r.refid,h.linkid,h.status,rf.RefId

Problem is, if the rating has 3 rating it showing 3 rows, if it has four rating it showing 4 rows, i used distinct in select statement but no use,

Please help me to get the correct query..

Thanks in Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 05:49:20

what rating you want show? recent or first?

esambath
Yak Posting Veteran

89 Posts

Posted - 2008-11-06 : 06:09:37

Hi visakh,

Thank you for your reply

i need to show the rating for the particular reference, for example where refid=16,

Expected Output

refid title description url point catid averagerating linkid

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 07:31:47

quote:
Originally posted by esambath

Hi visakh,

Thank you for your reply

i need to show the rating for the particular reference, for example where refid=16,

Expected Output

refid title description url point catid averagerating linkid

Thanks in advance

nope. you overlook my point. i was asking what rating you want if reference contains more than one rating records associated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources