I am unsure if I followed your example completely, but maybe this might help. Not knowing your complete database structure, I am unsure if my comments will be entirely valid but here goes.
I think you could achieve your goal if you think of your groups as containing one or many clients. Each single client would be in a group of their own. Yes, this is a little strange, but it makes the structure a lot easier and consistent. So this is what you would have:
Time slot --< class --< group --< client
So the structure, in english:
Each time slot has a one-to-many relationship to classes
Each class has a one-to-many relationship to groups
Each group has a one-to-many relationship to clients
Now, you can query the database and see how many time slots have more than one class.
You no longer need to worry about double booking.....
Hope this helps........
At 11:23 AM 1/1/03 -0500, David T-G wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi, all --
I need, I think, some pointers to basic JOIN tutorials. I don't really
know how to approach this query. I should say early on that I don't
expect the list to write my code for me, though any help anyone can send
is VERY much appreciated; rather, I don't even know where to look to do
the required reading :-)
I have a schedule table that looks like
create table schedule
(
# ID number
id smallint not null default 0 auto_increment primary key ,
class smallint not null , # references classtypes.id
client smallint not null , # references client.id
instr smallint not null , # references personnel.id
place smallint not null , # references places.id
timeslot datetime not null , # when
#unique (timeslot,client) , # no double-bookings
#unique (timeslot,instr) , # no double-bookings
index (timeslot,client) , # no double-bookings
index (timeslot,instr) , # no double-bookings
index (timeslot,place) , # cannot be unique 'cuz of group classes
cancelled datetime not null # cancelled? when?
) ;
that holds my bookings. Classes can be either private (one client) or
group (some number N, though perhaps only 1 client will sign up).
I started out, as you can see, with unique indexes for the client and
instructor, but since I had the brilliant idea of creating some N rows
for a group class, all with empty client fields, that doesn't work. I'm
not so worried about that; it just means that I'm going to have to do
some work on my own to ensure no double-bookings (except for a group
class). [OK, so maybe it wasn't that brilliant; better approaches will
be heard with avid interest. But it worked in my *head*! :-]
Then I had the idea of using client id '0', which will never occur in the
client table (create table clients ( id smallint unsigned not null
default 0 auto_increment primary key , ...);), as a way to black out a
time slot so that the instructor can be guaranteed a lunch break or so.
That's where my problem really came up.
Thinking at first only of an instructor and single clients I came up with
(deep breath)
select substring(s.timeslot,1,13) , concat(c.fname,' ',c.lname) from personnel as i , clients as c , schedule as s where i.id = s.instr and c.id = s.client and i.fname = 'penelope' order by timeslot;
which gives me a lovely
+----------------------------+-----------------------------+
| substring(s.timeslot,1,13) | concat(c.fname,' ',c.lname) |
+----------------------------+-----------------------------+
| 2002-12-27 06 | david t-g |
| 2002-12-27 07 | david t-g |
| 2002-12-27 10 | david t-g |
| 2002-12-27 11 | harmon |
| 2002-12-27 13 | larry thorburn |
+----------------------------+-----------------------------+
and I write my table in php without a second thought. Then, however,
comes the mess of pulling out any records where the client id is 0; for
every timeslot like that, I get a row for each client in the clients
table!
Here's where I'm really swamped. I can write a separate query for
schedule records where the client is 0, and I can write a separate query
for schedule records where the class is not private, but how can I
combine all three to get one lovely result to use to build my table?
TIA & HAND & Happy New Year
mysql query,
:-D
- --
David T-G * There is too much animal courage in
(play) [EMAIL PROTECTED] * society and not sufficient moral courage.
(work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health"
http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (FreeBSD)
iD8DBQE+ExYNGb7uCXufRwARAp0EAJ9a5C3emiflZGtuiXPOcWnMJo7iXQCeKVMw
0w2kNXILUltbWs/rxUwG22E=
=kYIn
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
