Try:
SELECT C.category_name, SUM( IF( ISNULL( P.product_ID ),0, 1) ) AS total
FROM category AS C LEFT JOIN product AS P
ON C.category_ID = P.category_ID
GROUP BY C.category_ID
Ignatius
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 18, 2002 10:09 AM
Subject: [PHP-DB] joining 2 tables - help please..
> I have 2 tables as follows:
>
> product table
> | product_id | name | category_id
> | 1| coffee| 2
> | 3| orange|1
> | 5| mango|1
> | 2| tea |2
>
> and
> category table
> |category_id | category_name |
> |2| drinks|
> |1| fruit |
> |4| desert|
> |5| main |
>
> I want to get how many product in each category using sql statement, but I
> keep getting NULL for the one that doesn;t have product link. I want to
get
> result from all category like this: drinks (2), fruit(2), desert(0),
> main(0).
> Help please...
>
>
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
