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.pigg...@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
