Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    12

    Question Unanswered: [SELECT] question

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i babble often

    i'm wrong occasionally

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Posts
    12
    Thank you all very much !!
    Paolo Saudin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •