April 13, 2009 at 11:47 am
Please help me to sort out my problem!!!
I have 2 tables:
1. News (NewsID, UserID, Content)
2. User (UserID, Name)
I want to return a table such as:
| UserID | Name | Number of news posted by this user |
------------------------------------------------------
I believe I should use a T-SQL such as:
CREATE PROCEDURE [dbo].[User_Get_All]
AS
SELECT
User.UserID,
User.Name,
COUNT ?????? "Number of news posted by this user"
FROM
User INNER JOIN News ON News.UserID = User.UserID
Thanks in advance
April 13, 2009 at 12:33 pm
Hey,
Here's the Soln....
select
user.userid,
user.name,
count(news.userid)
from user
inner join news on user.userid = news.userid
group by user.userid,
user.name
April 13, 2009 at 12:54 pm
sreddy (4/13/2009)
selectuser.userid,
user.name,
count(news.userid)
from user
inner join news on user.userid = news.userid
group by user.userid,
user.name
Why count(userid)?
j1a4l0: What do you want returned for a user that has no news items?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 13, 2009 at 1:03 pm
sreddy (4/13/2009)
--------------------------------------------------------------------------------
select
user.userid,
user.name,
count(news.userid)
from user
inner join news on user.userid = news.userid
group by user.userid,
user.name
Why count(userid)?
j1a4l0: What do you want returned for a user that has no news items?
--------------------------------------------------------------------------------
Gail Shaw
thats a good question Gail Shaw, left join will take care of the users who doesnt posted the news...
select
user.userid,
user.name,
count(news.userid)
from user
left join news on user.userid = news.userid
group by user.userid,
user.name
April 13, 2009 at 1:24 pm
Thanks guys, I got it!!!!
April 13, 2009 at 2:53 pm
sreddy (4/13/2009)
sreddy (4/13/2009)--------------------------------------------------------------------------------
select
user.userid,
user.name,
count(news.userid)
from user
inner join news on user.userid = news.userid
group by user.userid,
user.name
Why count(userid)?
j1a4l0: What do you want returned for a user that has no news items?
--------------------------------------------------------------------------------
Gail Shaw
thats a good question Gail Shaw, left join will take care of the users who doesnt posted the news...
select
user.userid,
user.name,
count(news.userid)
from user
left join news on user.userid = news.userid
group by user.userid,
user.name
Off topic @sreddy
To "quote" a previous post just click the "Quote" button instead of the "Reply" button. Then you get the well formed and already quoted post within the message text box. 🙂
Greets
Flo
April 13, 2009 at 3:33 pm
Florian Reischl (4/13/2009)
sreddy (4/13/2009)
sreddy (4/13/2009)--------------------------------------------------------------------------------
select
user.userid,
user.name,
count(news.userid)
from user
inner join news on user.userid = news.userid
group by user.userid,
user.name
Why count(userid)?
j1a4l0: What do you want returned for a user that has no news items?
--------------------------------------------------------------------------------
Gail Shaw
thats a good question Gail Shaw, left join will take care of the users who doesnt posted the news...
select
user.userid,
user.name,
count(news.userid)
from user
left join news on user.userid = news.userid
group by user.userid,
user.name
Off topic @sreddy
To "quote" a previous post just click the "Quote" button instead of the "Reply" button. Then you get the well formed and already quoted post within the message text box. 🙂
Greets
Flo
Thanks Florian Reischl.....Will follow nw on wards.....
SKewl.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply