Trying to do a blog join (entry columns, and # of comments) without success

12 Sep 2006 10:21 PM

ffrugone

Like everyone else in the known world, I'm programming my own blog.
Only, I'm not figuring out, (like so many others) how to do a single
query to get both each of my indiviual blog entries and the count of
the number of comments for each of those entries.

I am trying to get this output:

id        date          title   description CountOfNumberOfComments
---------------------------------------------------------------------------------
1    9/12/2006     First      blah,...                     3
2    9/12/2006   Second   blah,...                     0
3    9/12/2006    Third      blah,...                    12

I have two tables: Announcements and Comments

Comments has the column 'itemID' which is the foreign key for the 'id'
column on Announcements

So, far Ive tried:

SELECT TOP (5) Announcements.id, Announcements.itemdate,
Announcements.title, Announcements.description, Announcements.photo,
COUNT(Comments.itemid) AS CountOfNumberOfComments
FROM Announcements INNER JOIN
               Comments ON Announcements.id = Comments.itemid
WHERE (Comments.itemtype = 'entry')
ORDER BY Announcements.itemdate DESC

but it produces many errors, and even if it didn't, it would produce
multiple lines for each blog entry, (one for each comment).

Can anybody help me?

12 Sep 2006 10:34 PM

Chris Lim

ffrug***@gmail.com wrote:

> I am trying to get this output:
>
> id        date          title   description CountOfNumberOfComments
> ---------------------------------------------------------------------------------
> 1    9/12/2006     First      blah,...                     3
> 2    9/12/2006   Second   blah,...                     0
> 3    9/12/2006    Third      blah,...                    12

Either:

SELECT TOP 5
                Announcements.id,
                Announcements.itemdate,
                Announcements.title,
                Announcements.description,
                COUNT(Comments.itemid) AS CountOfNumberOfComments
FROM Announcements
LEFT OUTER JOIN Comments
    ON Comments.itemid = Announcements.id
    AND Comments.itemtype = 'entry'
GROUP BY    Announcements.id,
            Announcements.itemdate,
            Announcements.title,
            Announcements.description
ORDER BY Announcements.itemdate DESC

Or:

SELECT TOP 5
                Announcements.id,
                Announcements.itemdate,
                Announcements.title,
                Announcements.description,
                CountOfNumberOfComments =
                    (   SELECT COUNT(*)
                        FROM    Comments
                        WHERE   Comments.itemid = Announcements.id
                        AND        Comments.itemtype = 'entry' )
FROM Announcements
ORDER BY Announcements.itemdate DESC

13 Sep 2006 12:25 AM

ffrugone

Thanks, worked like a charm.

Chris Lim wrote:
Show quoteHide quote

> ffrug***@gmail.com wrote:
> > I am trying to get this output:
> >
> > id        date          title   description CountOfNumberOfComments
> > ---------------------------------------------------------------------------------
> > 1    9/12/2006     First      blah,...                     3
> > 2    9/12/2006   Second   blah,...                     0
> > 3    9/12/2006    Third      blah,...                    12
>
> Either:
>
> SELECT TOP 5
>                 Announcements.id,
>                 Announcements.itemdate,
>                 Announcements.title,
>                 Announcements.description,
>                 COUNT(Comments.itemid) AS CountOfNumberOfComments
> FROM Announcements
> LEFT OUTER JOIN Comments
>     ON Comments.itemid = Announcements.id
>     AND Comments.itemtype = 'entry'
> GROUP BY    Announcements.id,
>             Announcements.itemdate,
>             Announcements.title,
>             Announcements.description
> ORDER BY Announcements.itemdate DESC
>
> Or:
>
> SELECT TOP 5
>                 Announcements.id,
>                 Announcements.itemdate,
>                 Announcements.title,
>                 Announcements.description,
>                 CountOfNumberOfComments =
>                     (   SELECT COUNT(*)
>                         FROM    Comments
>                         WHERE   Comments.itemid = Announcements.id
>                         AND        Comments.itemtype = 'entry' )
> FROM Announcements
> ORDER BY Announcements.itemdate DESC