Here is one way. Some time ago I set up a table named count with one field named count and built 5000 rows of incrementing values .
I think I originally populated it by originally createing it with a 2nd field CREATE TABLE `count` ( `count` int(10) unsigned NOT NULL auto_increment, `addr_ID` int(10) NULL, <<<<<<<<<<<<<<<< this field does not really matter PRIMARY KEY (`count`) ) ENGINE=InnoDB DEFAULT CHARSET=; and then doing something like INSERT INTO count(addr_ID) SELECT addr_ID <<<<<<< any primary key out of any table with > 5000 entries FROM addresses LIMIT 5000; and then droping the 2nd field. Just put an INSERT in front of the select and set the value = to the # of dates you want to populate and the set value to 1 day pior to where you want to start. mysql> set @d:='2004-12-31 00:00:00'; Query OK, 0 rows affected (0.00 sec) mysql> select @d:[EMAIL PROTECTED] + interval 1 day as date from count where count <= 10; +---------------------+ | date | +---------------------+ | 2005-01-01 00:00:00 | | 2005-01-02 00:00:00 | | 2005-01-03 00:00:00 | | 2005-01-04 00:00:00 | | 2005-01-05 00:00:00 | | 2005-01-06 00:00:00 | | 2005-01-07 00:00:00 | | 2005-01-08 00:00:00 | | 2005-01-09 00:00:00 | | 2005-01-10 00:00:00 | +---------------------+ 10 rows in set (0.00 sec) -----Original Message----- From: Jonathan Mangin [mailto:[EMAIL PROTECTED] Sent: Monday, July 18, 2005 8:03 AM To: Michael Stassen Cc: mysql@lists.mysql.com Subject: Re: Impossible join? > Jonathan Mangin wrote: > >> Hello all, >> >> I'm storing data from a series of tests throughout each >> 24-hour period. I thought to create a table for each test. >> (There are six tests, lots more cols per test, and many >> users performing each test.) > > But each test is performed no more than once per day by a given user? Correct. > >> select test1.date, test1.time, test2.date, test2.time from >> test1 left join test2 on test2.date=test1.date where >> test1.date between '2005-07-01' and '2005-07-16' and >> uid='me'; > > Something is strange here. Doesn't uid exist in both tables? I'll assume > it does. Oops. Also correct. > >> +------------+------+------------+------+ >> | date | time | date | time | >> +------------+------+------------+------+ >> | 2005-07-13 | 6:30 | 2005-07-13 | 7:30 | >> | 2005-07-14 | 6:32 | 2005-07-14 | 7:45 | >> | 2005-07-15 | 6:30 | 2005-07-15 | 7:42 | >> | 2005-07-16 | 6:35 | NULL | NULL | >> +------------+------+------------+------+ >> >> Is there a join, or some other technique, that would >> return (nearly) these same results if test1 (or any test) >> has not been performed? Using 4.1.11. >> >> TIA, >> Jon > [ SNIP! ] > A better solution would be to add a table: > > CREATE TABLE `testdates` (`date` date default NULL, > UNIQUE KEY `date_idx` (`date`) > ); > > Insert one row into testdates for each day. Now you can use something > like this: > > SELECT testdates.date, test1.time AS 'Test 1', test2.time AS 'Test 2' > FROM testdates > LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' > LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' > WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; > > +------------+----------+----------+ > | date | Test 1 | Test 2 | > +------------+----------+----------+ > | 2005-07-11 | NULL | NULL | > | 2005-07-12 | NULL | 07:28:00 | > | 2005-07-13 | 06:30:00 | 07:30:00 | > | 2005-07-14 | 06:32:00 | 07:45:00 | > | 2005-07-15 | 06:30:00 | 07:42:00 | > | 2005-07-16 | 06:35:00 | NULL | > +------------+----------+----------+ > 6 rows in set (0.01 sec) > > Much better, don't you think? This generalizes pretty well, too. > > SELECT testdates.date, > test1.time AS 'Test 1', > test2.time AS 'Test 2', > test3.time AS 'Test 3', > test4.time AS 'Test 4' > FROM testdates > LEFT JOIN test1 on testdates.date = test1.date AND test1.uid = 'me' > LEFT JOIN test2 on testdates.date = test2.date AND test2.uid = 'me' > LEFT JOIN test3 on testdates.date = test3.date AND test3.uid = 'me' > LEFT JOIN test4 on testdates.date = test4.date AND test4.uid = 'me' > WHERE testdates.date BETWEEN '2005-07-11' AND '2005-07-16'; > > +------------+----------+----------+----------+----------+ > | date | Test 1 | Test 2 | Test 3 | Test 4 | > +------------+----------+----------+----------+----------+ > | 2005-07-11 | NULL | NULL | NULL | 08:12:00 | > | 2005-07-12 | NULL | 07:28:00 | 07:14:00 | 08:14:00 | > | 2005-07-13 | 06:30:00 | 07:30:00 | 07:16:00 | 08:29:00 | > | 2005-07-14 | 06:32:00 | 07:45:00 | 07:14:00 | 08:26:00 | > | 2005-07-15 | 06:30:00 | 07:42:00 | 07:19:00 | NULL | > | 2005-07-16 | 06:35:00 | NULL | NULL | NULL | > +------------+----------+----------+----------+----------+ > 6 rows in set (0.00 sec) > > Michael > I'm guessing this is a common solution. Shame on me. How does one swiftly populate a table with an entire year (or more) of dates? Thanks very much, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
