Re: data type error in my query

From: -D- (noone_at_nospam.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 17:49:50 -0500

Hi Steve,
    Thanks so much for your help! It works...thanks!

I had to change the count to (C.blogID) instead of (*) in the query
otherwise, the resultset returned the same value for comments. Some values
should have been zero, others just 1, 10, etc., but it was returning 1 for
every record returned? Once I changed to (C.blogID) the values were all
correct. I don't know what caused that?

I'm still really new to using SQL. By chance, do you know of a good
reference book for a beginner? I'm just know learning about JOINS and using
COUNT.

SELECT TOP 5 W2.blogDate, W2.blogHeader, W2.blogComment, W2.blogPostTime,
G.Comments
FROM
(
SELECT W1.blogID, COUNT(C.blogID) AS Comments
FROM dbo.tblWeblog as W1 LEFT OUTER JOIN dbo.tblUserComments as C
ON W1.blogID = C.blogID
GROUP BY W1.blogID
)
G JOIN tblWeblog as W2
ON G.blogID = W2.blogID
ORDER BY W2.blogDate DESC

Thanks again,
Dwayne

"Steve Kass" <skass@drew.edu> wrote in message
news:uyZnnQQnEHA.596@TK2MSFTNGP11.phx.gbl...
> D,
>
> If blogID is a unique identifier for rows of tblWeblog, you can do this
> (and it will also prevent the off chance that two blogIDs match on the
> header, comment, and postTime columns). If not, replace blogID here with
> the column or columns that uniquely identify a tblWeblog row.
>
> select top 5 W2.blogDate, W2.blogHeader, W2.blogComment, W2.blogPostTime,
> G.Comments
> from (
> select W1.blogID, count(*) as Comments
> from dbo.tblWeblog as W1
> left outer join dbo.tblUserComments as C
> on W1.blogID = C.blogID
> group by W1.blogID
> ) G join tblWeblog as W2
> on G.blogID = W2.blogID
> order by W2.blogDate desc
>
> [untested]
>
> Steve Kass
> Drew University
>
>
> "-D-" <noone@nospam.com> wrote in message
> news:ey3JMDQnEHA.2764@TK2MSFTNGP10.phx.gbl...
> >I have the following query:
> >
> > SELECT TOP 5 W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime,
> > COUNT(C.blogID) AS Comments
> > FROM dbo.tblWeblog AS W LEFT OUTER JOIN dbo.tblUserComments AS C ON
> > W.blogID = C.blogID
> > GROUP BY W.blogDate, W.blogHeader, W.blogComment, W.blogPostTime
> > ORDER BY W.blogDate DESC
> >
> > I receive the error:
> > "The text, ntext, and image data types cannot be compared or sorted,
> > except
> > when using IS NULL or LIKE"
> >
> > After troubleshooting the error I discovered that GROUP BY doesn't allow
> > the
> > field blogComment because it uses a text data type, so that is why the
> > error
> > is thrown. If I strip out W.blogComment the query runs fine. Problem
is,
> > I
> > need to include the blogComment field in the resultset.
> >
> > So, how can I correct the above query to allow the field, W.blogComment,
> > within the statement and return its value in the resultset?
> >
> > Any help is greatly appreciated. Thanks.
> > -D-
> >
> >
>
>

Relevant Pages

  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... form's record source to be a query with an outer join of the ... This form is continuous (actually a subform), ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Cond. Formatting w/ DLookup
    ... I'd like to be able to indicate on the first form, ... IvId field, then a better way would be to change the first ... form's record source to be a query with an outer join of the ... Using your excellent idea of the outer join, ...
    (microsoft.public.access.formscoding)
  • Re: Optimising the Query
    ... SUM ... FROM TABLE3 ... The query is taking records from TABLE2 and TABLE3 and LEFT OUTER JOIN ...
    (comp.databases.oracle.misc)
  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)