Re: COUNT and OUTER JOIN results
| From: | Amit Tandon | Date: | Fri, 06 May 2011 09:49:02 +0000 |
| Subject: | Re: COUNT and OUTER JOIN results | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-47786@lists.php.net to get a copy of this message | ||
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.iV
> m
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_categoryKjAÿ
> 4 n).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 )
> P`
> ə,kprayer_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
>
>