"Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Instead, you want a query which returns a record
> with "thread count" and "message count" for each
> COUNT(DISTINCT threadid) AS threads,
> COUNT(*) AS messages
> thread LEFT JOIN message ON message.threadid=thread.id
> GROUP BY boardid ASC
> WHERE boardid IN ( 1, 2 )
Thanks for the query Hugh, but it gives me:
"You have an error in your SQL syntax near 'WHERE boardid IN(1,2)' at line
In the 5th line I changed "thread.id" to "thread.threadid" so the name was
correct but the same error occurs.
After much testing I managed to get the following:
COUNT(DISTINCT thread.threadid) AS threads,
COUNT(*) AS messages,
thread LEFT JOIN message ON message.threadid=thread.threadid
GROUP BY thread.boardid ASC
to give a perfectly satisfactory result but have no idea if this is
perversing SQL again or not! (it at least makes more sense now I guess).
As soon as I add the "where IN" clause it errors.
Can anyone recommend ANY good books on SQL query design? I don't want (or
care) about database administration, I just want to know what constitutes a
good database design and lots and lots of query examples/tutorials.
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php