Try this:
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.
>
>
>
> ---------------------------------------------------------------------
> 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
