thank you for explaining this to me Amit.  It works.  Ron

The Verse of the Day
“Encouragement from God’s Word”  

From: Amit Tandon 
Sent: Friday, May 06, 2011 5:49 AM
To: Ron Piggott 
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`

level` IN ( 1, 3 )
`prayer_requests`.`prayer_request_type` = 1


"The difference between fiction and reality? Fiction has to make sense."

On Fri, May 6, 2011 at 2:42 PM, Ron Piggott <> 

  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` = 
  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,


  The Verse of the Day
  “Encouragement from God’s Word” 

  PHP Database Mailing List (
  To unsubscribe, visit:

Reply via email to