Solved

MySQL Function with loop and concat

Posted on 2015-02-24
2
344 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 48

Accepted Solution

by:
PortletPaul earned 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

732 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