Re: COUNT and OUTER JOIN results

thank you for explaining this to me Amit. It works. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info From: Amit Tandon Sent: Friday, May 06, 2011 5:49 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] COUNT and OUTER JOIN results Dear Ron Take your condition to ON cluause. So your on clause (for LEFT JOIN) would read something like ON prayer_request_category.` reference = prayer_requests.prayer_request_category_reference` AND prayer_requests.`approval_ level` IN ( 1, 3 ) prayer_requests.prayer_request_type = 1 ============ regds amit "The difference between fiction and reality? Fiction has to make sense." On Fri, May 6, 2011 at 2:42 PM, Ron Piggott <ron.piggott@actsministries.org> wrote: The following query returns all 8 prayer request categories with the total # of requests every submitted to each category: SELECT prayer_request_category.reference , prayer_request_category.category , COUNT( prayer_requests.reference ) AS category_request_count FROM prayer_request_category LEFT OUTER JOIN prayer_requests ON prayer_request_category.reference = prayer_requests.prayer_request_category_reference GROUP BY prayer_request_category.reference ORDER BY prayer_request_category.category ASC I would like to add the following 2 WHERE conditions to this query so only the live prayer requests are included in the COUNT: prayer_requests.approval_level IN ( 1, 3 ) prayer_requests.prayer_request_type = 1 When I do this only the categories with live prayer requests are returned, instead of all 8 categories. Is there a way to build these WHERE conditions which will still allow all 8 categories to be included in the result? Thank you, Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php