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]
