Solved

MySQL Function with loop and concat

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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.​
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

828 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