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: johan.hook@stripped (Johan Hook)
>To: shaun thornburgh <shaunthornburgh@stripped>
>CC: paul@stripped, mysql@stripped
>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" <paul@stripped>
>>>To: <mysql@stripped>
>>>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:shaunthornburgh@stripped]
>>>Sent: Friday, June 18, 2004 7:54 AM
>>>To: mysql@stripped
>>>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/mysql?unsub=1
>>>
>>>
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives: http://lists.mysql.com/mysql
>>>To unsubscribe:
>>>http://lists.mysql.com/mysql?unsub=1
>>>
>>
>>_________________________________________________________________
>>It's fast, it's easy and it's free. Get MSN Messenger today!
>>http://www.msn.co.uk/messenger
>>
>>
>
>
>--
> - MailTo:johan.hook@stripped
> - http://www.pythagoras.se
>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
