At 9:14 -0500 4/4/02, George Smith wrote: >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'.
You need to create another table that has a row for each date in the range, then use that table in a LEFT JOIN to your original table to find rows in the date table that are not represented by your original table. I happen to have a DBI script make_date_list.pl that creates just such a table. Contact me offline if you'd like a copy. >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
