SELECT question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT question

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-19 : 11:51:42

Hello,

I have two tables with the following columns:
Posts > [PostId][AuthorId][PostTitle], [PostHtml] and [PostDate]
Users > [UserId][UserName][UserEmail]

[AuthorId] in Posts is the FK which "linked" to PK [UserId] in Users.

I need to create two SELECT codes:
1. Get a POST from POSTS where PostId = @PostId
And then get the UserName and UserEmail from Users.

2. Get all Posts, and for each post, also the UserName and UserEmail for a given time period (day, month or year).

How can I do this?

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 11:58:07


select *
from   Posts p inner join Users u
on     p.AuthorId = u.UserId
where  p.PostID = @PostId


select *
from   Posts p inner join Users u
on     p.AuthorId = u.UserId
where  p.PostDate >= '20070319'
and    p.PostDate <  '20070320

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-03-19 : 12:05:06

Hello,

I am not using Inner Join because I want to be able to return some feedback on either a user, post or something else exists or not ...

Should I always use Inner Join?

And what I have is:
-- Get blog post
SELECT
@PostTitle = PostTitle,
@PostHTML = PostHTML,
@PostUpdatedDate = UpdatedDate
FROM dbo.Posts WHERE PostId = @PostId

-- Get blog post author id
DECLARE @AuthorId AS UNIQUEIDENTIFIER
SELECT @AuthorId = AuthorId FROM dbo.by27_Posts WHERE PostId = @PostId

-- Get blog author
SELECT
@PostAuthorName = UserName,
@PostAuthorEmail = UserEmail,
@PostAuthorUrl = UserUrl
FROM dbo.Users WHERE UserId = @AuthorId

And for (2):
Can I get records grouped by month?
I will use it in a GridView where each page is a month.
So should I do this in my .NET code or in my SQL procedure?

Thanks,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources