[HELP] problem with JOIN ON and GROUP BY

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)

[HELP] problem with JOIN ON and GROUP BY

Author

Topic

starsmile
Starting Member

2 Posts

Posted - 2010-05-24 : 12:01:36

I have two tables

and

when i use

select b.BookID,b.BookName,b.Price,sum(Quantity) as SL from Books b
join OrderDetails o on b.BookID = o.BookID
group by b.BookID,b.BookName,b.Price order by SL desc

it works!

but when i want to get BookContent of Book , i use

select b.BookID,b.BookName,b.Price,b.BookContent,sum(Quantity) as SL from Books b
join OrderDetails o on b.BookID = o.BookID
group by b.BookID,b.BookName,b.Price,b.BookContent order by SL desc

it shows error

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

How can i get BookContent? I dont want to change type ntext of it
Thanks for replies

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-24 : 12:16:32

I can't see your pics coz its blocked within my n/w, but try converting to varchar(max) in the query alone

select b.BookID,b.BookName,b.Price,convert(varchar(max),b.BookContent),sum(Quantity) as SL from Books b 
join OrderDetails o on b.BookID = o.BookID 
group by b.BookID,b.BookName,b.Price,convert(varchar(max),b.BookContent) order by SL desc