Re: A Complicated Group Query

shaun thornburgh Tue, 22 Jun 2004 13:22:11 -0700

Hi Shawn,

A slight correction(!) Client_ID is contained in the Projects table as a Client can have many projects. Therefore c.Project_ID will cause an error...

Here is a definition of the tables:

mysql> DESCRIBE Projects;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| Project_ID   | int(11)      |      | PRI | NULL    | auto_increment |
| Project_Name | varchar(100) |      |     |         |                |
| Client_ID    | int(11)      |      |     | 0       |                |
+--------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> DESCRIBE Clients;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| Client_ID   | int(11)      |      | PRI | NULL    | auto_increment |
| Client_Name | varchar(100) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql>

Thanks a million for your help here :)

From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED]
Subject: Re: A Complicated Group Query
Date: Tue, 22 Jun 2004 14:32:14 -0400
OOPS! That's because my second query should have been a LEFT JOIN and not a
RIGHT JOIN (I hate making silly cut-and-paste errors like that) 8-{
If I understand you correctly, you want to see a count of how many bookings
you have had per project broken down by user location. Try this:
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
      on u.Client_ID = c.Client_ID
LEFT JOIN Bookings b
      on b.User_ID = u.User_ID
GROUP BY c.Project_ID, u.User_Location
Or for just one project, we can do this:
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
      on u.Client_ID = c.Client_ID
      and c.Project_ID = 'x'
LEFT JOIN Bookings b
      on b.User_ID = u.User_ID
GROUP BY c.Project_ID, u.User_Location
---  or this ----
SELECT c.Project_ID, u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
      on u.Client_ID = c.Client_ID
LEFT JOIN Bookings b
      on b.User_ID = u.User_ID
WHERE c.Project_ID = 'x'
GROUP BY c.Project_ID, u.User_Location
--- or even this ---
SELECT u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
INNER JOIN Users u
      on u.Client_ID = c.Client_ID
      and c.Project_ID = 'x'
LEFT JOIN Bookings b
      on b.User_ID = u.User_ID
GROUP BY u.User_Location
If you have any NULL "user_location"s that's because you have at least one
Client that is part of a project but that Client has no Users. In my last
sample I changed the first LEFT JOIN to an INNER JOIN so that I would only
retrieve those Clients (that are part of project 'x') that actually have
Users. I could have also eliminated them on the way out of the query (and
after the group by) like this
SELECT u.User_Location, COUNT(b.booking_ID) as Bookings
FROM Clients c
LEFT JOIN Users u
      on u.Client_ID = c.Client_ID
      and c.Project_ID = 'x'
LEFT JOIN Bookings b
      on b.User_ID = u.User_ID
GROUP BY u.User_Location
HAVING u.User_ID IS NOT NULL
As you can see, there are many ways to build the report you want and you
should use the one that performs best for you.
There _is_  a  difference in putting c.Project_ID = 'x' in the ON clause
and in the WHERE clause. In the ON clause those rows are eliminated BEFORE
the temporary internal table is built. In the WHERE clause you are
filtering on all of the rows. I am going to make up some round numbers to
illustrate. Say you have 50 Clients where 5 of them are part of project
'x'. You have 100 Users and 500 Bookings.
Every Client has at least 1 user and every User has at least 1 Booking
(just for argument's sake). This query:
SELECT *
FROM Clients, Users, Bookings
(or , alternatively)
SELECT *
FROM Clients
INNER JOIN Users
INNER JOIN Bookings
will contain all of the columns of each the three tables and will return
(50x100x500) or 2500000 rows. This is a "cartesian product" of the three
tables. The ON clauses of the JOIN statements keep that to a reasonable
number. Lets say that this is a perfectly balanced world and each User has
had 5 bookings and that each Client has 2 users then this query
SELECT *
FROM Clients c
INNER JOIN Users u
      ON u.Client_ID = c.Client_ID
INNER JOIN Bookings b
      ON b.User_ID = u.UserID
will result in only (50x2x5) or 500 rows. It's much easier for the WHERE
clause to scan the resulting columns (assuming no index exists) in a result
set this size than one containing 2.5 million rows.
Adding another restriction to one of the ON clauses helps us out even more
SELECT *
FROM Clients c
INNER JOIN Users u
      ON u.Client_ID = c.Client_ID
      AND c.Project_ID='x'
INNER JOIN Bookings b
      ON b.User_ID = u.UserID
Results in only (5x2x5) or 50 rows. If you had any additional restrictions
(like in a WHERE clause) you only have to apply those to a dataset of 50
rows. What if we had performed a GROUP BY?  In the first case we would have
had to GROUP 2.5 million rows, in the second 500 rows and in the last only
50.
You cannot always put all of your restrictions into the ON clause of a
JOIN. Let's say you wanted to know what Clients didn't have any users
assigned to them. You need this query:
SELECT c.Client_Name
FROM Clients c
LEFT JOIN Users u
      on u.Client_ID = c.Client_ID
WHERE u.Client_ID IS NULL
In this case you would need the _results_ of the JOIN in order to see which
rows didn't match up. You can't put the IS NOT NULL into the ON clause as
none of your Client's User_IDs are actually null. It's the internal results
of the LEFT JOIN that will have NULL values for all of your Users columns
for those rows where the IDs didn't match.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"shaun thornburgh" <[EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] otmail.com> cc: Fax to: 06/22/2004 12:08 Subject: Re: A Complicated Group Query PM
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


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

Re: A Complicated Group Query

Reply via email to