Thanks for your reply,
This works great, but when I add a constraint such as WHERE B.Project_ID = '10' I dont get the NULL values from the User table which I need:
SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID WHERE B.Project_ID = '8' GROUP BY(U.User_Location);
Any ideas, do I need to do a double join, or do I need to join the projects table also?
Thanks for your help
From: "Paul McNeil" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: RE: Group Query Date: Fri, 18 Jun 2004 08:20:10 -0400
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]
_________________________________________________________________
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]
