Hi Shaun,
You said:
>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.
That's exactly how the LEFT JOIN is supposed to work. The users are on the
LEFT side of the LEFT JOIN so you will get _all_ rows from that table and
only those rows from the table on the *other* side of the join that satisfy
your ON conditions.
You can flip your query two ways. Change either LEFT JOIN to RIGHT JOIN or
exchange the positions of the table names around the JOIN keywords
SELECT COUNT(B.Booking_ID), U.User_Location
FROM Users U
RIGHT OUTER JOIN Bookings B
ON U.User_ID = B.User_ID
AND B.Project_ID = '10'
GROUP BY(U.User_Location);
-OR-
SELECT COUNT(B.Booking_ID), U.User_Location
FROM Bookings B
RIGHT OUTER JOIN Users U
ON U.User_ID = B.User_ID
AND B.Project_ID = '10'
GROUP BY(U.User_Location);
Either one of these statements puts the Bookings table into the
"controlling" side of the partial join.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"shaun thornburgh"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
otmail.com> cc:
Fax to:
06/22/2004 09:41 Subject: A Complicated Group Query
AM
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
