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
