[SELECT] question

spol · #1 (**permalink**) 06-26-04, 02:19

Hi all,
I've got a table structured as follow where the code field identify a particular parameter. Is it possible with a single query to retrieve more than just one parameter at a time? I mean can I get a recordset with the first field representing the date, the second field holding the values of parameter code [01], the third field the values of parameter code [02] and so forth??

Table structure
CREATE TABLE `test` (
`date` datetime NOT NULL ,
`code` varchar(3) NOT NULL ,
`value` float NOT NULL
) TYPE=MyISAM;

Sample data
+---------------------+------+-------+
| date | code | value |
+---------------------+------+-------+
| 2004-06-01 00:00:00 | 001 | 89 |
| 2004-06-01 00:00:00 | 002 | 1 |
| 2004-06-01 01:00:00 | 001 | 76 |
| 2004-06-01 01:00:00 | 002 | 5 |
| 2004-06-01 02:00:00 | 001 | 67 |
| 2004-06-01 02:00:00 | 002 | 7 |
| 2004-06-01 03:00:00 | 001 | 46 |
| 2004-06-01 03:00:00 | 002 | 4 |
| 2004-06-01 04:00:00 | 001 | 43 |
| 2004-06-01 04:00:00 | 002 | 3 |
+---------------------+------+-------+

Recordset
+---------------------+------------+------------+
| date | value_cod1 | value_cod2 |
+---------------------+------------+------------+
| 2004-06-01 00:00:00 | 89 | 1 |
| 2004-06-01 01:00:00 | 76 | 5 |
| 2004-06-01 02:00:00 | 67 | 7 |
|................................................. ..................|
+---------------------+------------+------------+

Thank you very much!
Paolo Saudin

Pat Phelan · #2 (**permalink**) 06-26-04, 12:04

To select all of the columns in an arbitrary order, you can use:

Code:

SELECT *
   FROM test

To select the columns in a specific order, you can use:

Code:

SELECT date, code, value
   FROM test

-PatP

r937 · #3 (**permalink**) 06-26-04, 23:31

ah, the good old denormalization question

mysql doesn't support full outer join, but this is an equivalent --

Code:

select t1.date 
     , t1.value as value_cod1 
     , t2.value as value_cod2 
  from `test` as t1
left outer
  join `test` as t2
    on t1.date 
     = t2.date
   and t2.code = '002'  
 where t1.code = '001'     

union all

select t2.date 
     , null
     , t2.value 
  from `test` as t1
right outer
  join `test` as t2
    on t1.date 
     = t2.date  
   and t1.code = '001'  
 where t2.code = '002'
   and t1.date is null

you'll need mysql 4.0 to support union, and if you don't have any code 002 rows without a code 001 row, then you can get away with just the first of the two queries

Pat Phelan · #4 (**permalink**) 06-27-04, 12:17

Quote:

Originally Posted by r937

ah, the good old denormalization question

I wondered what on earth you were babbling about as I read your response, but when I went back and re-read the question carefully I discovered that you were correct! I've always preferred the structure:

Code:

SELECT date
,  Sum(CASE WHEN '001' = code THEN value END) AS '001'
,  Sum(CASE WHEN '002' = code THEN value END) AS '002'
,  Sum(CASE WHEN '003' = code THEN value END) AS '003'
   FROM test
   GROUP BY date

This only has to make one pass through the table, so it can be more efficeient if the optimiser does its job.

-PatP