I have a mysql table like this
mysql> describe reports;
+-+-+--+-++---+
| Field | Type| Null | Key | Default| Extra |
+-+-+--+-++---+
| rptdate | date| | PRI | -00-00 | |
| id | int(11) | | PRI | 0 | |
| summary | text| | || |
+-+-+--+-++---+
I can easily find what dates a given id has made a summary report
using a query like
mysql> select rptdate from reports where id=123 and
-> rptdate between '1998-01-01' and '2000-12-31';
++
| rptdate|
++
| 1998-01-02 |
| 1998-01-04 |
| 2000-12-30 |
| 2000-12-31 |
++
My question is how to write a query that finds the dates that
aren't in the database. For example, for the same range as
the sample, the first two items of interest would be
'1998-01-01' and '1998-01-03'.
I looked in the archive and I saw the recent discussions using
a join to find data that is in one table and not another. I
don't think that is applicable as I don't have a table of
all possible dates to compare with.
I see two solutions -- either create a temporary table containing
all possible dates in my range of interest, then using the
techniques in the archive, or get the list of dates with a
report present and then use an external program to find
the missing ones.
Is there a better way than these two? What would be the most
efficient method? All advice would be appreciated.
Thanks,
George
-
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
