Re: Impossible join?
On 7/16/05, Jonathan Mangin <[EMAIL PROTECTED]> 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.) > > 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'; > > ++--++--+ > | 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. You would need a full outer join, but MySQL doesn't support it... :( You need some way to hack around it. Maybe ditch the JOIN clauses and join tables in application, for instance using hashes? Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Impossible join?
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'; > > ++--+--+--+---
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]
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? 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 Ma
Impossible join?
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.) 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'; ++--++--+ | 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
