Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unanswered: HOWTO select several rows in one comma- separated

    Hello!!

    First of all, thank you in advance for helping me!!!

    My problem is that I have a BBDD with a table like this (in MySql)

    E.g.

    +---------+-----------+
    | groupId | serviceId |
    +---------+-----------+
    | grup1 | service1 |
    | grup1 | service2 |
    | grup1 | service3 |
    | grup2 | service1 |
    | grup2 | service2 |
    +---------+-----------+

    And I need to do a select o a procedure or something that returns me something like this:

    +---------+------------------------------------------+
    | groupId | serviceId |
    +---------+------------------------------------------+
    | grup1 | service1, service2, service3 |
    +---------+------------------------------------------+

    Is this possible in any way????

    Than you very very much.

    Almu

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Almu

    If you have a function then you could just loop through the records adding each item to a string and then return the string. The problems with this is that it's poor design - what happens when the number of items exceeds the max string length for MySQL (255 chars I believe).

    select get_all_service_ids( groupId );

    Better to just pull all the items back as a normal select then use PHP (or whatever your front end is written in) to build the string.

    What's a BBDD?

    Mike

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there any max number of services per group?
    Unfortunately you're delving into the realms of recursion as Mike suggested, which is horrible!

    What is the justification for showing the results in this way?
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2007
    Posts
    4
    Thank you for you answers!!!

    Unfortunately there isn't a maximun number of services per group...

    I'll try to post process them after the select...

    Than you anyway!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I haven't got MySQL where I am but the code would be similar to the following but you'd want to check the size of the resulting string:

    Code:
    create function  get_all_service_ids( 
        p_groupId varchar(20) 
    ) returns varchar(255)
    as
    begin
        declare v_loop varchar(20);
        declare v_str varchar(255);
    
        select  min(serviceId) into v_loop
        from    MyTable
        where  groupId = p_groupId;
    
        while ( v_loop is not null ) do
           if v_str is null then
               set v_str = v_loop;
           else
               set v_str = concat( v_str, ',',  v_loop );
           end if;
    
           select  min(serviceId) into v_loop
           from    MyTable
           where  groupId = p_groupId
                     and serviceId > v_loop;
        end while;
    
        return( v_str );
    end;
    you're delving into the realms of recursion
    Strictly speaking it's iteration.

    Mike

  6. #6
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242

  7. #7
    Join Date
    Aug 2007
    Posts
    4
    Thank youuuuu a lot!!

    Someone has given me the solution, it's with the function GROUP_CONCAT that you've told me:

    SELECT groupId, GROUP_CONCAT(serviceId) AS serviceIds FROM group_services where groupId=’grupo1’ GROUP BY groupId;

    But I think that the problem of the max size is still there, isn't it?

    Almu

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by mike_bike_kite
    Strictly speaking it's iteration.
    What's the difference?
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you're only running the query for a single group at a time, then you can simplify this --

    SELECT groupId, GROUP_CONCAT(serviceId) AS serviceIds FROM group_services where groupId='grupo1' GROUP BY groupId;

    to this --

    SELECT GROUP_CONCAT(serviceId) AS serviceIds FROM group_services where groupId='grupo1'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2007
    Posts
    4
    Oh, yes, that second one is what I need!

    Thank you!!!

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Learnt something there - thanks.
    Mike

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally Posted by georgev
    What's the difference?
    iteration: is just a loop (for, while etc). The idea is you repeat a process, usually on each item in an array etc, until finished.

    recursion: is where a function that calls itself repeatedly until a condition is met. This isn't done in a loop but by going ever deeper into into itself.

    Games playing programs such as this checkers program would iterate through all the moves at the current level and after each move recursively call itself to play all the moves of the oponent. This is called a Min-Max search tree where we look for the best move for us (the max score) followed by the best oponents move (the min score for us).

    Mike

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wow, I pretty much hit the nail on the head with the difference between the two (they were in my head and I comtemplated having a guess at them ).

    Thanks Mike
    George
    Home | Blog

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If you get some time then have a play at the checkers program, it was an earlly attempt by me at a Java applet but it plays reasonably well.
    Mike

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've actually played it before
    George
    Home | Blog

Posting Permissions

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