RE: Select dates from Table 1 not in Table 2
> At 11:55 23/09/01 +1000, you wrote: > 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'll take my foot out of my mouth now and try and look at what I'm doing! Thanks Will, you were spot on. The sql example you provided did exactly what it was suppose to, I just didn't add all the extra grouping and so when I looked, only one date showed as valid in the table. Which, ungrouped is correct :) Thanks also Will, for all the extra bits you added, I'll go over those now and start to build a slightly more complex processing list now incorporating all the locations, cast, crew and other resources needed and of course, now the dates will show up by group :) /me pulls foot out of mouth and looks at ALL data, rather than an assumed snapshot :) - 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
RE: Select dates from Table 1 not in Table 2
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 > FROMschedule LEFT JOIN nonavailable ON schedule.date = > nonavailable.date; > > SELECT date > FROMtmp_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 2069Phone (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
RE: Select dates from Table 1 not in Table 2
Try this: CREATE TEMPORARY TABLE tmp_avldate (date datetime, nadate datetime); INSERT INTO tmp_avldate (date, nadate) SELECT DISTINCT schedule.date, nonavailable.date FROMschedule LEFT JOIN nonavailable ON schedule.date = nonavailable.date; SELECT date FROMtmp_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
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
