Try this:
SELECT news.NEWSID, news.DATETIME, authors.AUTHOR, count(comments.CID) as
NUMCOMS from news LEFT JOIN authors ON news.AUTHORID=authors.AUTHORID LEFT
JOIN comments ON news.NEWSID=comments.NEWSID GROUP BY news.NEWSID ORDER BY
news.DATETIME;
I havent tested this, and you may have to add news.DATETIME to the GROUP BY
clause, but this should work AFAIK. If you send create scripts + test data
for the tables, you're more likely to get a faster response...
dpk
----- Original Message -----
From: "Chad Day" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 11:53 AM
Subject: problems with a fairly basic SELECT/JOIN...
> Sending this out again, haven't gotten any help yet.. anyone?
>
> -----Original Message-----
> From: Chad Day [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 14, 2001 9:38 AM
> To: mysql
> Subject: problems with a fairly basic join..
>
>
> been banging at this for a while, can't get my head around it.
>
> 3 tables, one called news that stores news items, one called comments that
> stores user comments, one called authors that holds the author of the news
> items name. I am trying to display the news item with the authors name,
and
> count the # of comments on the news item.
>
> The closest I've come is:
>
> SELECT DISTINCT news.NEWSID, COUNT(*) as NUMCOMS from comments, news left
> join authors on news.AUTHORID = authors.AUTHORID where comments.NEWSID =
> news.NEWSID GROUP by comments.NEWSID, news.NEWSID ORDER BY news.DATETIME
> DESC;
>
> but that obviously is flawed in this respect: 'where comments.NEWSID =
> news.NEWSID' as if there are no comments, nothing matches and no news item
> is returned.
>
> I can't seem to figure out exactly what to do, I'm hoping someone can
help..
> below are my table structures.
>
> mysql> describe comments;
> +----------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+---------------+------+-----+---------+----------------+
> | CID | int(11) | | PRI | 0 | auto_increment |
> | NEWSID | int(11) | | | 0 | |
> | USERID | int(11) | | | 0 | |
> | TITLE | text | | | NULL | |
> | COMMENT | blob | | | NULL | |
> | DATETIME | timestamp(14) | YES | | NULL | |
> +----------+---------------+------+-----+---------+----------------+
>
> mysql> describe news;
> +-----------+---------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+---------------+------+-----+---------+----------------+
> | NEWSID | int(11) | | PRI | 0 | auto_increment |
> | AUTHORID | tinyint(4) | | | 0 | |
> | TITLE | tinytext | | | NULL | |
> | LINE | tinytext | | | NULL | |
> | LEAD | blob | | | NULL | |
> | REMAINING | blob | | | NULL | |
> | DATETIME | timestamp(14) | YES | | NULL | |
> | CATEGORY | tinytext | | | NULL | |
> +-----------+---------------+------+-----+---------+----------------+
>
> mysql> describe authors;
> +----------+-------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+-------------+------+-----+---------+----------------+
> | AUTHORID | int(11) | | PRI | 0 | auto_increment |
> | AUTHOR | tinytext | | | NULL | |
> | PASSWORD | varchar(16) | | | | |
> | EMAIL | tinytext | | | NULL | |
> +----------+-------------+------+-----+---------+----------------+
>
> Thanks for any help!
> Chad
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
