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?
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.
+------------+------+------------+------+
| 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
With "test1 LEFT JOIN test2", you get dates where test1 was performed, but not
test2. With "test2 LEFT JOIN test1", you would get dates where test2 was
performed but not test1. You could combine the two with a UNION to include
both possibilities. You'd want to see the results in order by date, so you'd
have to do a tricky ORDER BY on the UNION. Given only the tables you
describe, something like this should (almost) do:
(SELECT test1.date d1, test1.time t1, test2.date d2, test2.time t2
FROM test1
LEFT JOIN test2 ON test1.date=test2.date AND test1.uid=test2.uid
WHERE test1.uid = 'me'
AND test1.date between '2005-07-01' and '2005-07-16')
UNION DISTINCT
(SELECT test1.date d1, test1.time t1, test2.date d2, test2.time t2
FROM test2
LEFT JOIN test1 ON test1.date=test2.date AND test1.uid=test2.uid
WHERE test2.uid = 'me'
AND test2.date between '2005-07-11' and '2005-07-16')
ORDER BY COALESCE(d1, d2);
+------------+----------+------------+----------+
| d1 | t1 | d2 | t2 |
+------------+----------+------------+----------+
| NULL | NULL | 2005-07-12 | 07:28:00 |
| 2005-07-13 | 06:30:00 | 2005-07-13 | 07:30:00 |
| 2005-07-14 | 06:32:00 | 2005-07-14 | 07:45:00 |
| 2005-07-15 | 06:30:00 | 2005-07-15 | 07:42:00 |
| 2005-07-16 | 06:35:00 | NULL | NULL |
+------------+----------+------------+----------+
5 rows in set (0.01 sec)
(COALESCE(list) returns the first non-NULL value in the list.)
Ugly, isn't it? And the ORDER BY at the end uses filesort, which isn't
desirable. Also, did you notice that no test was run on the 11th? Hard to
see things which aren't there. This doesn't generalize very well, either.
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
