Select Count and * from two joined tables

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 2000 Forums

Transact-SQL (2000)

Select Count and * from two joined tables

Author

Topic

apwestgarth
Starting Member

10 Posts

Posted - 2006-12-12 : 14:53:46

Hi I'm trying to do something which should be really simple but I don't seem to be able to get the syntac correct :-(

Posts Table
Id PK, int not null
Title varchar(255) not null
Message text not null
PostDate datetime not null
CategoryID int not null

Comments Table

Id int not null
PostId int not null
Title varchar 255 not null
Message varchar 8000 not null
PostedBy varchar255 not null
Url varchar 8000 null alllowed
PostDate datetime not null

I am trying to select * from Posts table and a count of the comments per post from the comments table. The posts table is joined to the comments table - posts.id = comments.postid.

I have tried the following

SELECT p.Message, p.Id, p.Title, p.PostDate, p.CategoryID, Count(dbo180920232.Blog_Comments.Id) AS Expr1
FROM dbo180920232.Blog_Posts AS p FULL OUTER JOIN
dbo180920232.Blog_Comments ON p.Id = dbo180920232.Blog_Comments.PostId
ORDER BY p.PostDate DESC

but keep getting the following error:

Column 'dbo180920232.Blog_Posts.Message' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can anyone help or point me in the right direction please?

Thanks

Andrew

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-12 : 14:56:14

You need a GROUP BY.

I'll assume you want to group by these columns, but I could be wrong:
GROUP BY p.Message, p.Id, p.Title, p.PostDate, p.CategoryID

If the grouping is wrong, then we'll need to a derived table to get the other columns you want returned. Let us know.

Tara Kizer

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 15:08:27

try this...

select p.title, p.message, p.postdate, p.categoryid, count(c.postid) as 'count'
from posts p
left join comments c
on p.postid = c.postid
group by p.title, p.message, p.postdate, p.categoryid

apwestgarth
Starting Member

10 Posts

Posted - 2006-12-12 : 15:13:37

HI I've tried the following:

SELECT p.Title, p.PostDate, p.CategoryId, p.[Message], Count(dbo180920232.Blog_Comments.Id) AS Expr1
FROM dbo180920232.Blog_Posts AS p FULL OUTER JOIN
dbo180920232.Blog_Comments ON p.Id = dbo180920232.Blog_Comments.PostId
group by dbo180920232.Blog_Comments.Id, p.Title, p.PostDate, p.CategoryId, p.[Message]

and it worked until I added p.[Message] ( a text field from db) to the query and I got the following message:

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

Thanks

Andrew

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 15:23:45

select p.title,cast(p.message as nvarchar), p.postdate, p.categoryid, count(c.postid) as 'count'
from posts p
left join comments c
on p.postid = c.postid
group by p.title, cast(p.message as nvarchar), p.postdate, p.categoryid

apwestgarth
Starting Member

10 Posts

Posted - 2006-12-12 : 15:25:42

quote:
Originally posted by cognos79

select p.title,cast(p.message as nvarchar), p.postdate, p.categoryid, count(c.postid) as 'count'
from posts p
left join comments c
on p.postid = c.postid
group by p.title, cast(p.message as nvarchar), p.postdate, p.categoryid

That works ! Great Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-12 : 15:47:52

It might not give you the correct data though. You need to figure out exactly what columns achieve the correct count. That'll determine what columns go in the GROUP BY.

It is a newbie mistake to just keep adding columns to the GROUP BY until the errors go away. You might not get errors, but the data is incorrect.

Tara Kizer

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 16:00:19

what Tara said is right...determine exactly what fields you need in the select statement. that will avoid grouping by all the fields...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources