You can use a Left Outer Join. Left Outer Join will include all that matches as well as that which doesn't. The resulting NULL entries for the count will evaluate to 0.
SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID GROUP BY(U.User_Location); God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -----Original Message----- From: shaun thornburgh [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 7:54 AM To: [EMAIL PROTECTED] Subject: Group Query Hi, The following table produces a query that shows all bookings that user has made and groups the number of bookings by the users location code. mysql> SELECT COUNT(B.Booking_ID), User_Location FROM Bookings B, Users U WHERE U.User_ID = B.User_ID GROUP BY(U.User_Location); At the momnet if no bookings are made by a particular user then their location isnt included in the result. Is it possible to modify this query so that if no bookings are made for a particlar location then the location is still included in the result i.e. +---------------------+---------------+ | COUNT(B.Booking_ID) | User_Location | +---------------------+---------------+ | 1 | 01 | | 8 | 02 | | 9 | 03 | | 1 | 04 | | 1 | 05 | | 2 | 06 | | 1 | 07 | ... Many thanks for your help _________________________________________________________________ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
