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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
