Thanks Will for the suggestion, however as per my private email to you, it
didn't work successfully to any degree. Actually it knocked out ONE date
from the "same" group and that was one of the Available dates.
I have created a tmp_table for use, for some reason I can't create
TEMPORARY tables properly, not that it matters, if needed I'm happy for a
junk table to use as part of the process.
But the key issue is what I need to do and that again is described at the
bottom of this message. If I can't find a solution that works, I'm going
to have to call in five or six people to manually draw up white boards of
dates for the next three months and start allocating 200 people and 42
locations to each date. My need below is only the very beginning of an
enormous film schedule system I need for in-house use.
Because of the nature of 200 cast members and 42 locations and the
availability of both, the secondary issue is crew. We need to cross ref
who is available on which days, in which groups and for which
locations. Not everyone is allocated to the same location obviously. Crew
need to be selected from the available skills as well, because it's no good
going to a shoot and finding you don't have a principal cast member, a
cameraman or a location!
Anyway the date comparison and selection is very critical. I also need to
build an override in case dates don't work out and we have limited choice
but to tell people they are on that date. But that's VERY easy :)
Anyway details of Will's suggestion and my detailed explanation are below.
> CREATE TEMPORARY TABLE tmp_avldate (date datetime, nadate datetime);
>
> INSERT INTO tmp_avldate (date, nadate)
> SELECT DISTINCT schedule.date, nonavailable.date
> FROM schedule LEFT JOIN nonavailable ON schedule.date =
> nonavailable.date;
>
> SELECT date
> FROM tmp_avldate
> WHERE nadate is NULL;
>
> DROP TABLE tmp_avldate;
>
>Regrettably, the fact that it takes 4 steps may not be as simple as you
>would like... but with only a max of 200 output records, it should be plenty
>fast.
>
> > -----Original Message-----
> > From: Adam Todd [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, September 21, 2001 12:29 AM
> > To: [EMAIL PROTECTED]
> > Subject: Select dates from Table 1 not in Table 2
> >
> >
> > Hi all, just subscribed. Normally I work these out for myself, but time
> > and frustration are winning.
> >
> > I have two tables:
> >
> > SCHEDULE which contains a column called "date" and in that are DATETIME
> > entries from 2001-09-14 to who cares.
> >
> > NONAVAIALBLE which contains users and dates they aren't available in a
> > column called 'nonavaildate'
> >
> > I needed to create 'schedule' because I can't find an easy way to
> > generate
> > a list of dates 200 days long without going to external scripts, which is
> > fine on my unix systems, but I have windows systems too. (sigh) It also
> > means I've got an indexed reference point that I can use for other things
> > anyway.
> >
> > What I need to do is list dates in ranges, say for example between
> > 2001-11-01 and 2001-11-10 that have no comparable entries in nonavailable.
> >
> > I've tried all kinds of things and given up to ask the wider audience out
> > there! Any ideas welcome, but it must be done within SQL, preferably as
> > generic as possible as I have that mixed platform issue.
> >
> > My last attempt was:
> >
> > SELECT date
> > FROM schedule, nonavailable
> > WHERE registrationid=30 #don't worry about that
> > AND date >= "2001-11-01" and date <= "2001-11-10"
> > AND nonavaildate >= "2001-11-01" and nonavaildate <= "2001-11-10"
> > AND date <> nonavailadate
> > GROUP BY date
> > ORDER BY date
> >
> > It unfortunately dumps a list of dates from 1st to 10th :)
> >
> > If I: SELECT count(date), date
> >
> > I get a result of dates but the count varies according to which dates are
> > not in table2 (nonavailable).
> >
> > Any suggestions, hints, ideas. The simpler the better.
> >
---------------------------------------------------------------------------
Iconoclast Productions [EMAIL PROTECTED]
A Division of Todd Corporation Pty Limited http://iconoclast.inoz.com/
THE ALL IMPORTANT ONLINE SCHEDULE SYSTEM http://schedule.ip.inoz.com/php/
PO BOX 174, ROSEVILLE NSW 2069 Phone (02) 8825 6111 Fax (02) 8825 6199
Producing "Caught in the Net" Late 2001 for Cannes 2002
http://iconoclast.inoz.com/tjps/
---------------------------------------------------------------------
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
