Count - 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

Count

Author

Topic

Moo
Starting Member

10 Posts

Posted - 2008-07-30 : 10:09:03

Hi Friends,

These are the tables A,B,C
A table had columns Articleid ,userid and ArticleName
B table had Columns Comments ,Articleid and userid
c table had Columns voting ,Articleid and userid

i need your help to select Articleid,ArticleName ,Comments,no of Comments ,Voting for that particular userid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-30 : 10:12:14

Similar to your the other thread. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107391

Do you try to understand the query i posted ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 10:14:03

quote:
Originally posted by Moo

Hi Friends,

These are the tables A,B,C
A table had columns Articleid ,userid and ArticleName
B table had Columns Comments ,Articleid and userid
c table had Columns voting ,Articleid and userid

i need your help to select Articleid,ArticleName ,Comments,no of Comments ,Voting for that particular userid

your result can be achieved by simply joining the tables involved on related columns and aggregating to find count. Try it out yourself and post if you face any difficulty.

Moo
Starting Member

10 Posts

Posted - 2008-07-30 : 10:23:14

But i cant get the comments for that particular article

Moo
Starting Member

10 Posts

Posted - 2008-07-30 : 10:30:16

select top 10 e.Articleid ,e.Articlename ,r.Voting ,n.comments,
count(n.Articlecommentsid)as noofcomments from A e
join B n on e.Articleid=c.Articleid

join C r on e.Articleid=r.Articleid where e.deleted=0 and e.userid=27
group by e.Articleid,e.Articlename, r.Voting,n.comments
order by e.Articleid desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 10:49:50

[code]SELECT a.Articleid,a.ArticleName,b.Comments,t.cmntcnt,c.voting
FROM A a
INNER JOIN B b
ON b.Articleid=a.,Articleid
AND b.userid=a.userid
INER JOIN C c
ON c.Articleid=a.,Articleid
AND c.userid=a.userid
INNER JOIN (SELECT Articleid,userid,COUNT(Comments) AS cmntcnt
FROM B
GROUP BY Articleid,userid)t
ON t.Articleid=a.,Articleid
AND t.userid=a.userid[/code]

Moo
Starting Member

10 Posts

Posted - 2008-07-30 : 11:13:09

Here i am getting the Articleid,ArticleName,Comments and No of comments ,rating but the ArticleName should not be repeated

Cowboy
Yak Posting Veteran

72 Posts

Posted - 2008-07-30 : 12:24:12

Group by ArticleName perhaps?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

Moo
Starting Member

10 Posts

Posted - 2008-07-30 : 12:29:08

Then too i can't get the Correct answer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:14:24

quote:
Originally posted by Moo

Here i am getting the Articleid,ArticleName,Comments and No of comments ,rating but the ArticleName should not be repeated

how can you get unique values of ArticleName & id when you've more than 1 comments existing for them?
Alternatively if you want only 1 record per articlke, then which comment value do you want to show for them as you can show only single value. or do you want to combine all comments and return as a single value?

Moo
Starting Member

10 Posts

Posted - 2008-07-31 : 01:12:50

I need to combine all comments and return as a single value

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 01:22:52

Post
1. your table DDL in terms of CREATE TABLE . . .
2. some sample data in terms of INSERT INTO table . . .
3. and also the expected result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources