Hi,
Thanks for your reply Johan, this works however I actually onley wanted the rows where Project_ID = '10'.
SELECT COUNT(B.Booking_ID), U.User_Location FROM Users U LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '10' GROUP BY(U.User_Location);
This query is returning all of the users in the user table regardless of the Project_ID...
So what I need is a query that returns a count of bookings for all user locations where the Project_ID = 'X'
Thanks again for your help
From: [EMAIL PROTECTED] (Johan Hook) To: shaun thornburgh <[EMAIL PROTECTED]> CC: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Group Query Date: Tue, 22 Jun 2004 10:56:12 +0200
Shaun, when you add "WHERE B.Project_ID > = '10'" you, in a way, change your LEFT JOIN to an INNER JOIN. You need to do it like: LEFT OUTER JOIN Bookings B ON U.User_ID = B.Rep_ID AND B.Project_ID = '8'
/Johan
shaun thornburgh wrote:
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
-- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77
_________________________________________________________________ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]