Re: A Complicated Group Query

shaun thornburgh Tue, 22 Jun 2004 09:15:58 -0700

Hi Shawn,

Thanks for your reply, I am still having problems here though!

The first query produces this:

+---------------------+---------------+
| COUNT(B.Booking_ID) | User_Location |
+---------------------+---------------+
|                1818 | NULL          |
|                   1 | 204           |
|                   1 | 301           |
|                   1 | 302           |
|                   1 | 408           |
+---------------------+---------------+

The location counts here are correct except for the NULL, but the other locations havent been included.

The second query, produces a complete list of all the Users locations!

There is another table that might help here; Clients. Each User is referenced to a client and each project is referenced to a client. So a client has many projects and many users in the database. If I modify my initial 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 U.Client_ID = 'X' GROUP BY(U.User_Location);

It works perefectly for all clients that have one project. But I need to be able to produce the same report for individual projects rather than clients!

I hope you can help me here, many thanks!

From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: A Complicated Group Query
Date: Tue, 22 Jun 2004 10:12:22 -0400


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]

_________________________________________________________________ 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]

Re: A Complicated Group Query

Reply via email to