HOWTO select several rows in one comma- separated

almu_alf · #1 (**permalink**) 08-21-07, 05:37

mike_bike_kite · #2 (**permalink**) 08-21-07, 06:54

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

gvee · #3 (**permalink**) 08-21-07, 07: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?

almu_alf · #4 (**permalink**) 08-21-07, 08:08

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!

mike_bike_kite · #5 (**permalink**) 08-21-07, 08:17

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;

Quote:

you're delving into the realms of recursion

Strictly speaking it's iteration.

Mike

dbmab · #6 (**permalink**) 08-21-07, 08:28

This might help - http://dev.mysql.com/doc/refman/5.0/...n_group-concat

almu_alf · #7 (**permalink**) 08-21-07, 08:41

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

gvee · #8 (**permalink**) 08-21-07, 08:54

Quote:

Originally Posted by mike_bike_kite

Strictly speaking it's iteration.

What's the difference?

r937 · #9 (**permalink**) 08-21-07, 09:07

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'

almu_alf · #10 (**permalink**) 08-21-07, 09:10

Oh, yes, that second one is what I need!

Thank you!!!

mike_bike_kite · #11 (**permalink**) 08-21-07, 10:23

Learnt something there - thanks.
Mike

mike_bike_kite · #12 (**permalink**) 08-21-07, 10:40

Quote:

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