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

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-04-30 : 10:00:35

Hello,

I am selecting some articles and some comments related with it:

SELECT
a.ArticleID,
a.Title,
a.Content,
c.CommentId,
c.Title,
c.Comment,
u.UserName AS ArticleAuthorName,
u.UserEmail AS ArticleAuthorEmail
FROM Articles a
INNER JOIN Users u ON a.AuthorID = u.UserID
INNER JOIN Comments c ON a.ArticleID = c.ArticleID

I have 2 problems which I am trying to solve:
1. Comments table also have an AuthorId
So for each comment I also want to join to Users table and get
the author name and email. How can I do this?

2. I want to select all Articles even if it has comments or not.
Can I use Inner Join or should I use Left Join?

Is Outer Join still available in SQL 2005?

Thank You,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-30 : 10:03:34

Try this


SELECT
       a.ArticleID,
       a.Title,
       a.Content,
       c.CommentId,
       c.Title,
       c.Comment,
       u.UserName AS ArticleAuthorName,
       u.UserEmail AS ArticleAuthorEmail,
       uc.UserName AS CommentAuthorName,
       uc.UserEmail AS CommentAuthorEmail
FROM Articles a
INNER JOIN Users u ON a.AuthorID = u.UserID
LEFT JOIN Comments c ON a.ArticleID = c.ArticleID
LEFT JOIN Users cu ON c.AuthorID = cu.UserID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources