[thelist] Incorrect SQL?

[Joshua Olson]

----- Original Message -----
From: "Lachlan Cannon" <luminosity at members.evolt.org>
Sent: Saturday, April 06, 2002 12:34 AM
Subject: [thelist] Incorrect SQL?


> I got the SQL for it
> from Rudy, but have modified it a bit, and I think I might have broken it
so
> that it's not returning anything. Can anyone see what's wrong with it?
>
> $sql = "SELECT entryTitle, DATE_FORMAT(entryDate,'%d-%m-$Y') as entryDate,
> DATE_FORMAT(entryDate,'%W, %M %D') as entryLongDate,
> DATE_FORMAT(entryDate,'%T') as entryTime, entryEntry, count(*) as
> entryComments FROM blogEntries, blogComments WHERE blogEntries.entryId =
> blogComments.entryId GROUP BY entryTitle, entryDate, entryEntry ORDER BY
> entryDate DESC LIMIT 7";

Lach,

Multiple GROUP BY's is always where I get a bit shaky.  I tend to drift
towards trying to minimize the complexity of the query.  Some key
observations about your query:

1.  You are grouping by Title, Date, and the Entry... this may not be
necessary, depending on what you want to do.
2.  Your count(*) does not dictate "what" to count.

Let's take a look at a possible new version of the query:

SELECT
    entryTitle
  , DATE_FORMAT(entryDate,'%d-%m-$Y') as entryDate
  , DATE_FORMAT(entryDate,'%W, %M %D') as entryLongDate
  , DATE_FORMAT(entryDate,'%T') as entryTime,
  , entryEntry
  , count(blogComments.*) as entryComments
FROM blogEntries, blogComments
WHERE blogEntries.entryId = blogComments.entryId
GROUP BY blogEntries.entryId
ORDER BY 2 DESC
LIMIT 7

What the query does is return the joined set of Entries to Comments.  This
table will have n time m records.  Then, the GROUP BY summarizes by the PK
in the Entries table by summing up the number of comments in the related
table.  In this instance, you can return row level detail from the Entries
table under the assumption that entryId is the PK of the table.  The
database should recognize that data selected from a table whose PK in
included in the GROUP BY are ok for selection WITHOUT an aggregate function.
If EntryId is NOT the PK, then this query should be invalid--unless you
include an aggregate function around the items in the select.

If EntryId is NOT the PK but you know it's unique anyway, you could
technically wrap all the of NON-GROUPED fields in the SELECT statement with
Min() or Max(), just to make the parser happy.

Also, if entryId is NOT the PK, is there any reason it shouldn't be?

Oh yeah, some databases do not like ORDER BY on a calculated field.  Better
to use the column number, usually.  In this case, I use ORDER BY 2, which
indicates to use the second column--the entryDate column

Too durn early for me!  HTH,

-joshua