need of 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

need of query

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 02:28:54

Hi friends,

I am using 4 tables namely reference,referencehistory,referencerating,category

In referenec table i am having the fields refid,catid
In category i am having the fields catid,catname
In referencehistory i am having the fields refid,title,url,desc
In referencerating i am having the fields refid,points

I need to fetch the values of refid,url,title,desc,points,totalno of members rated according to the paricular category

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:34:46

what according to you is rule for calculating totalno of memebers?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 02:38:08

it should be calculated in referncerating table, according to the refid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 02:45:23

quote:
Originally posted by vidhya

it should be calculated in referncerating table, according to the refid

so you want total no of references to be returned with each reference info for a category?
Also are you using sql 2005?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 02:55:47

i need count of refid in referencerating table. i am using sql 2000

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 03:09:35

select h.title as title,h.description as description,h.url as url,rf.points as points,r.catid as catid,r.refid as refid from reference r inner join category c on c.catid=r.catid and r.deleted=0 and r.catid=32 inner join referencehistory h on h.refid=r.refid left join referencerating rf on r.refid=rf.refid

This is query i used, but i did not know how to fetch total no of members.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 04:30:14

quote:
Originally posted by vidhya

select h.title as title,h.description as description,h.url as url,rf.points as points,r.catid as catid,r.refid as refid from reference r inner join category c on c.catid=r.catid and r.deleted=0 and r.catid=32 inner join referencehistory h on h.refid=r.refid left join referencerating rf on r.refid=rf.refid

This is query i used, but i did not know how to fetch total no of members.

select h.title as title,h.description as description,h.url as url,rf.points as points,r.catid as catid,r.refid as refid,cnt.membercount 
from reference r inner join category c on c.catid=r.catid and r.deleted=0  and r.catid=32 inner join referencehistory h on h.refid=r.refid  
left join referencerating rf on r.refid=rf.refid 
left join (select count(refid) as membercount,catid
           from referenec 
           group by catid)cnt
on cnt.catid=c.catid

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 04:56:25

I got like this
testing testing http://www.hotmail.com 2 32 11 1
testing testing http://www.hotmail.com 2 32 11 1

but,I need to get like this

testing testing http://www.hotmail.com 2 32 11 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:09:06

quote:
Originally posted by vidhya

I got like this
testing testing http://www.hotmail.com 2 32 11 1
testing testing http://www.hotmail.com 2 32 11 1

but,I need to get like this

testing testing http://www.hotmail.com 2 32 11 2

but didnt you tell you need refid also along with results?

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 05:21:44

sorry i forget it,

but,I need to get like this

testing testing http://www.hotmail.com 2 32 11 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:40:53

[code]select h.title as title,h.description as description,h.url as url,rf.points as points,r.catid as catid,COUNT(r.refid) AS membercount
from reference r inner join category c on c.catid=r.catid and r.deleted=0 and r.catid=32 inner join referencehistory h on h.refid=r.refid
left join referencerating rf on r.refid=rf.refid
GROUP BY h.title ,h.description,h.url ,rf.points,r.catid [/code]

vidhya
Posting Yak Master

108 Posts

Posted - 2008-10-20 : 05:44:42

Thanks a lot and a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 05:45:53

quote:
Originally posted by vidhya

Thanks a lot and a lot

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources