Sql server query help - 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)

Sql server query help

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2009-10-20 : 13:45:05

Consider I have 4 tables as follows

Books(BookId,Name,AuthorId)
Books_Comments(BookId,CommentId)
Comments(CommentId,comment,CreatedAt)
Authors(AuthorId,AuthorName)

A single book can have many comments

select Books.BookId,Books.Name,Authors.AuthorName,Comments.comment
from Books

inner join Books_Comments.BookId=Books.BookId
inner join Comments on Comments.CommentId=Books_Comments.CommentId
inner join Authors on Authors.AuthorId=Books.AuthorId

where Authors.AuthorName='ABC'

I am looking for a query which returns me the last entered comment for the books.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-20 : 13:47:17

Use GROUP BY along with MAX.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 13:52:45

[code]select Books.BookId,Books.Name,Authors.AuthorName,Comments.comment
from Books
inner join (select BookId,MAX(CommentId) AS Latest
from Books_Comments
group by BookId)bc
on bc.BookId=Books.BookId
inner join Comments on Comments.CommentId=bc.Latest
inner join Authors on Authors.AuthorId=Books.AuthorId
[/code]

DesiGal
Starting Member

31 Posts

Posted - 2009-10-20 : 14:17:56

Thanks Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-20 : 14:20:25

welcome

leah
Starting Member

2 Posts

Posted - 2009-10-20 : 14:28:01

Hi...I had a sql question...I want to do something in sql....like this...

I have a sql data source and I pass 1 parameters if id = chani but 2 parameters if id = malka and if id = malka then parameterA is for sure NOT NULL, soo I wanted to do something like this, but I think it's wrong...could you direct me maybe??

@parameterA
int ,
@parameterB
char(12),
AS
BEGIN
SET NOCOUNT ON;
IF @parameterA is not NULL --malka

BEGIN
SELECT * FROM
Malka
WHERE parameterA = @parameterA AND parameterB=@parameterB
ORDER BY enteredDate DESC
END
ELSE -- if chani

BEGIN
SELECT myname, * FROM chani
WHERE parameterB = @parameterB
END
END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-20 : 14:30:13

Leah,

Don't hijack someone else's thread. Start a new thread, you'll get better results that way as well.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources