Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Function with loop and concat

Posted on 2015-02-24
2
Medium Priority
?
402 Views
Last Modified: 2015-02-25
Hi Experts,

I'm trying get all the product sizes into a column of a select concatenated by "/".
I'm trying to do this using a function that i called getproduct_sizes.
Something like:
SELECT tb.id,tb.ref,tb.description,getproduct_sizes(tb.id) sizes FROM product tb
Result:
id | ref | description | sizes
1 | ref1 | description1 | XS / XL
2 | ref1 | description1 | XL / XXL
3 | ref1 | description1 | XS / XXL / XXXL

Open in new window

I tried to create the function like this, but gives me allways NULL (The query of the table tb_productsize returns rows):
CREATE DEFINER = 'root'@'%'
FUNCTION getproduct_sizes(productid int)
  RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
  DETERMINISTIC
BEGIN
  DECLARE size_list varchar(4000);
  DECLARE v_productid int DEFAULT 0;
  DECLARE v_finished integer DEFAULT 0;
  DECLARE v_psize varchar(100) DEFAULT "";
  -- SET v_productid := productid;

  -- declare cursor for size product
  DECLARE psize_cursor CURSOR FOR
  SELECT
    tb.description
  FROM tb_productsize tb
  WHERE tb.id > productid;

  -- declare NOT FOUND handler
  DECLARE CONTINUE HANDLER
  FOR NOT FOUND SET v_finished = 1;

  OPEN psize_cursor;
    get_sizes:
    LOOP
    
      FETCH psize_cursor INTO v_psize;
    
      -- build email list
      SET size_list := CONCAT(v_psize, " / ", size_list);

      IF v_finished = 1 THEN
        CLOSE psize_cursor;
        LEAVE get_sizes;
      END IF;
    END LOOP get_sizes;
  RETURN size_list;
END

Open in new window


I Also tried this, but says "Result consisted of more than one row":
CREATE DEFINER = 'root'@'%'
FUNCTION getproduct_sizes(productid int)
  RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
  DETERMINISTIC 
BEGIN
    DECLARE x INT;
    DECLARE y INT;
    DECLARE size_list varchar(4000);
    SET x = productid;
    sloop:LOOP
        SET y = NULL;
        SELECT tb.description INTO y FROM tb_productsize tb WHERE id=x;
        SET size_list := CONCAT(y, " / ", size_list);
        ITERATE sloop;
    END LOOP;
    RETURN size_list;
END

Open in new window


Can anyone tell me what i'm doing wrong? Or maybe this is not possible :(

Thx in advanced,
Miguel
0
Comment
Question by:justaphase
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40629558
Why can't you use GROUP_CONCAT() ?

To assist more with this I think we need some "sample & representative data"

i.e. per table involved, supply a few rows of data.
But please ensure that if NULLs are part of the problem that NULLS are represented in the data you provide.
0
 
LVL 1

Author Closing Comment

by:justaphase
ID: 40630218
Portlet ! You're a life saver!
I didn't knew that MySQL function, it's mind blowing!!
I usually work with MsSQL, but i'm starting to like MySQL a bit more each day :D

So the query i wanted, and works, is this.
The table "tb_productproductsize" is the table that makes the connection between "tb_product" (the products) and "tb_productsize" (the sizes):
SELECT tp.id,tp.ref,tp.design,GROUP_CONCAT(DISTINCT ps.description ORDER BY ps.description SEPARATOR ' / ') sizes FROM tb_product tp
  LEFT JOIN tb_productproductsize tps ON tps.motherid=tp.id
  LEFT JOIN tb_productsize ps ON tps.childid=ps.id
GROUP BY tp.id

Open in new window


Thx!
Miguel
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question