mysql for loop or result in one row one column

Discussion in 'MySQL' started by dhiman_pawan, Sep 30, 2009.

0
  1. #1
    hi my problem is to show all colors of product in single column, in single row below is the query , its show multiple row from sub query error , i want all result of color will come in single column seprated by comma

    for example if a product has 3 to 5 color in attributesvalue table than a single column "color" with single row comes and show Red,green, blue as values in color column


    select
    p.productName,
    ( select categoryName from category c where c.id=p.id) as categoryName,
    ( select av.valueName from attributesvalue av,modelattributes ma where ma.attributevalueid=av.valueid and ma.productid =p.productid) as color,
    from product p
    ORDER BY p.productName
     
    dhiman_pawan, Sep 30, 2009 IP
  2. phones2me

    phones2me Peon

    Messages:
    35
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    you need top alter the way your presentation layer works, not your data layer
     
    phones2me, Oct 14, 2009 IP
  3. mastermunj

    mastermunj Well-Known Member

    Messages:
    687
    Likes Received:
    13
    Best Answers:
    0
    Trophy Points:
    110
    #3
    you can use group_concat here... it is good to collect data in one element only in comma separated manner.

    example:

    select
    p.productName,
    ( select categoryName from category c where c.id=p.id) as categoryName,
    group_concat(( select av.valueName from attributesvalue av,modelattributes ma where ma.attributevalueid=av.valueid and ma.productid =p.productid)) as color,
    from product p
    GROUP BY p.productName
    ORDER BY p.productName
     
    mastermunj, Oct 16, 2009 IP