I have a mysql table like this
mysql> describe reports;
+---------+---------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+------------+-------+
| rptdate | date | | PRI | 0000-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 |
<many rows omitted>
| 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
