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
