COUNT and OUTER JOIN results

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

Re: COUNT and OUTER JOIN results

--00151744866c8256db04a2986754
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Dear Ron

Take your condition to ON cluause. So your on clause (for LEFT JOIN) would
read something like

ON `prayer_request_category`.`
reference` =3D `prayer_requests`.`prayer_request_category_reference`
AND

`prayer_requests`.`approval_
level` IN ( 1, 3 )
`prayer_requests`.`prayer_request_type` =3D 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
wrote:

> The following query returns all 8 prayer request categories with the tota=
l
> # of requests every submitted to each category:
>
> SELECT `prayer_request_category`.`reference` ,
> `prayer_request_category`.`category` , COUNT( `prayer_requests`.`referenc=
e`
> ) AS category_request_count
> FROM `prayer_request_category`
> LEFT OUTER JOIN `prayer_requests` ON `prayer_request_category`.`reference=
`
> =3D `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 onl=
y
> the live prayer requests are included in the COUNT:
>
> `prayer_requests`.`approval_level` IN ( 1, 3 )
> `prayer_requests`.`prayer_request_type` =3D 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 conditi=
ons
> which will still allow all 8 categories to be included in the result?
> Thank you,
>
> Ron
>
> The Verse of the Day
> â€=9CEncouragement from Godâ€=99s Wordâ€=9D
> http://www.TheVerseOfTheDay.info
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--00151744866c8256db04a2986754--

Re: COUNT and OUTER JOIN results

------=_NextPart_000_0071_01CC0BD1.683A3FA0
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info =20


From: Amit Tandon=20
Sent: Friday, May 06, 2011 5:49 AM
To: Ron Piggott=20
Cc: php-db@lists.php.net=20
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`.`=20
reference` =3D `prayer_requests`.`prayer_request_category_reference`
AND=20

`prayer_requests`.`approval_=20
level` IN ( 1, 3 )
`prayer_requests`.`prayer_request_type` =3D 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 =
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` =3D =
`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` =3D 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
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info=20

--=20
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



------=_NextPart_000_0071_01CC0BD1.683A3FA0--