List:General Discussion« Previous MessageNext Message »
From:Jonathan Mangin Date:July 18 2005 1:03pm
Subject:Re: Impossible join?
View as plain text  
> 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


Thread
Impossible join?Jonathan Mangin16 Jul
  • Re: Impossible join?Michael Stassen16 Jul
  • Re: Impossible join?Jonathan Mangin18 Jul
  • Re: Impossible join?Dawid Kuroczko18 Jul
RE: Impossible join?Gordon Bruce18 Jul