SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member greenfin's Avatar
    Join Date
    Oct 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to present data with duplicate values, transform column to rows?

    Hello All,

    A food database contains nutrient values for thousands of food items. Different food items will contain different levels of different nutrients so it's unpredictable how many 'rows' will be returned for a simple query of a food item's content.

    Queries produced several rows of data for each food item, using this code:

    Code:
    SELECT nut_data.NDB_No, `Shrt_Desc` , `Nutr_Val` , nut_data.Nutr_No AS `nn`
    FROM `nut_data` , `food_des`
    WHERE nut_data.NDB_No = food_des.NDB_No
    AND (
    nut_data.Nutr_No = '204'
    OR nut_data.Nutr_No = '601'
    OR nut_data.Nutr_No = '605'
    OR nut_data.Nutr_No = '606'
    OR nut_data.Nutr_No = '645'
    OR nut_data.Nutr_No = '646'
    )
    Data array looks like (food item, nutrient, value):

    food item #1, 204, 88.1
    food item #1, 601, 0.89
    food item #1, 606, 1.2
    food item #1, 645, 3.0
    food item #2, 204, 88.3
    food item #2, 601, 2.3
    food item #2, 645, 1.3 ...

    but I need to print the data like so, where the different nutrients are placed in columns for each food item (food item, value for nutrient 204, value for nutrient 601, value for nutrient 605, and so on...):

    food item #1, 88.1, 0.89, 1.2, 3.0
    food item #2, 88.3, 2.3, 0, 1.3 ...

    Since we don't know up front if every nutrient is included in a certain food item, it would be important to place zeroes for any nutrients that are lacking.

    Unions didn't work due to the different sizes of result sets, (error #1222 - The used SELECT statements have a different number of columns)

    For example, this code produced the above error:

    Code:
    SELECT nut_data.NDB_No, `Shrt_Desc` , `Nutr_Val` , nut_data.Nutr_No AS `nn`
    FROM `nut_data` 
    LEFT JOIN `food_des`
    ON nut_data.NDB_No = food_des.NDB_No
    WHERE nut_data.Nutr_No = '204'
    UNION
    SELECT nut_data.NDB_No, `Nutr_Val`
    FROM `nut_data` 
    LEFT JOIN `food_des`
    ON nut_data.NDB_No = food_des.NDB_No
    WHERE nut_data.Nutr_No = '601'
    SO...is there a neater way to make my query, or should I figure out how to use PHP to make all the values for a given food item appear in one row - like transforming the nutrient value column into rows based on the food item?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    doing it in php is a very practical solution, since the query will be very fast

    doing it with UNIONs is also practical, provided that you return the same columns --
    Code:
    SELECT nut_data.NDB_No
         , Shrt_Desc 
         , Nutr_Val 
         , nut_data.Nutr_No AS nn
      FROM nut_data 
    LEFT OUTER
      JOIN food_des
        ON food_des.NDB_No = nut_data.NDB_No
     WHERE nut_data.Nutr_No = '204'
    UNION ALL
    SELECT nut_data.NDB_No
         , NULL
         , Nutr_Val
         , NULL
      FROM nut_data 
    LEFT OUTER
      JOIN food_des
        ON food_des.NDB_No = nut_data.NDB_No
     WHERE nut_data.Nutr_No = '601'
    this leads to the obvious question: what does 601 have in the Shrt_Desc and Nutr_No columns? if it isn't NULL, why isn't it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member greenfin's Avatar
    Join Date
    Oct 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937 - I was thinking about UNIONs in the wrong way, in that I was hoping to get a column slapped onto the end of the first SELECT (thinking that the number of rows had to match up), instead of attaching another long set of rows to the output of the first SELECT. Now that that's clarified, I don't know if UNIONs are the way to go as I would have to do 5 of them making the output very long - and still have to use PHP to produce a meaningful chart.

    To answer your question, the 601 (and other nutrients) has the Shrt_Desc and Nutr_No columns - I just didn't put it in the query as I was confusing rows and columns.

    Thank you for your input. I'll use PHP to massage the data into a readable chart - not sure how yet, but I've been reading the PHP forum for ideas.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    might want to try this: (sorry, don't have time right now to use your table/column names)
    Code:
    select f.name
         , nv1 as calories
         , nv2 as carbs
      from food f
      join nutrient_values nv1
        on nv1.food_id = f.id
       and nv1.type = 'calories'
      join nutrient_values nv2
        on nv2.food_id = f.id
       and nv2.type = 'carbs'
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Member greenfin's Avatar
    Join Date
    Oct 2005
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey longneck,

    Sorry so long to reply, but I did try your idea. The nv1 and nv2 nutrient values are not columns, but rather values in the column for all nutrient values for all food items. So, I can't see how to grab certain values within the column. The two table structures are like so, (where the second table is needed just to pick up the food item description):

    CREATE TABLE `nut_data` (
    `NDB_No` varchar(5) NOT NULL,
    `Nutr_No` varchar(3) NOT NULL,
    `Nutr_Val` decimal(10,3) NOT NULL,
    ...a few more rows here...)

    and

    CREATE TABLE `food_des` (
    `NDB_No` varchar(5) NOT NULL,
    `FdGrp_Cd` varchar(4) NOT NULL,
    `Long_Desc` varchar(200) NOT NULL,
    `Shrt_Desc` varchar(60) NOT NULL,
    ...a few more rows here...)

    This query returns an empty data set:

    Code:
    SELECT nut_data.NDB_No AS `ndb` , nv1.Shrt_Desc AS `desc` , nut_data.Nutr_Val AS `nv` , nut_data.Nutr_No AS `nn`
    FROM `nut_data`
    JOIN `food_des` nv1 ON nut_data.NDB_No = nv1.NDB_No
    AND nut_data.Nutr_No = '204'
    JOIN `food_des` nv2 ON nut_data.NDB_No = nv2.NDB_No
    AND nut_data.Nutr_No = '601'
    See anything I'm missing?


Bookmarks

Posting Permissions

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