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