Hi,
I have three tables (among others!) in my database; Bookings, Users and Projects. Users have location codes and can make bookings which are for a particular project.
Using the following query I can get a list of all Locations (Each user has a Location code) and the number of Bookings that have taken place for that location.
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);
This is useful as it shows zero values where no bookings have taken place. However, Each booking is related to a particular project by Project_ID. How can I modify this query such that it only returns a count of bookings where the Project_ID = 'x'? If I modify this query to:
SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID AND B.Project_ID = '10' GROUP BY(U.User_Location);
It returns all User Locations in the User table, regardless of the Project_ID of the Booking. And if I modify the query to:
SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.User_ID WHERE B.Project_ID = '10' GROUP BY(U.User_Location);
I lose the zero/NULL Values...
Any comments here would be greatly apprecited!
_________________________________________________________________
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]
