I would like to create an sql select that listed all group records as
a concatenated string, but I just can not work out how to do it.

eg Table1 Clothes Colours
COLOUR
red
blue
green
yellow

Table2 Clothes
COLOUR ITEM
red pullover
green jumper
green trousers
yellow shirt
red hat
red scarf
red coat

I want to be able to create a Select with two fields COLOUR, ITEMs,
which does the following, where ITEMS is concatenated from each
matching group record in Table2 by COLOUR
COLOUR ITEMS
red pullover: hat: scarf: coat
green jumper: trousers
yellow shirt


I hope I have explained it correctly.

Any help or pointers, would be most appreciated, even if it is - NOT
POSSIBLE

Thanks

Richard

mcl schreef:
> I would like to create an sql select that listed all group records as
> a concatenated string, but I just can not work out how to do it.
>
> eg Table1 Clothes Colours
> COLOUR
> red
> blue
> green
> yellow
>
> Table2 Clothes
> COLOUR ITEM
> red pullover
> green jumper
> green trousers
> yellow shirt
> red hat
> red scarf
> red coat
>
> I want to be able to create a Select with two fields COLOUR, ITEMs,
> which does the following, where ITEMS is concatenated from each
> matching group record in Table2 by COLOUR
> COLOUR ITEMS
> red pullover: hat: scarf: coat
> green jumper: trousers
> yellow shirt
>
>
> I hope I have explained it correctly.
>
> Any help or pointers, would be most appreciated, even if it is - NOT
> POSSIBLE
>
> Thanks
>
> Richard

maybe you can start with this, and work on it to get what you need:

mysql> select * from PROJECTS;
+--------+----------+
| projid | projname |
+--------+----------+
| 1 | Bravo |
| 2 | Alhpa |
| 3 | Omega |
+--------+----------+
3 rows in set (0.00 sec)

mysql> set @name=""; select (projid mod 2) as k, projid,
@name:=concat(@name,': ',projname) from PROJECTS;
Query OK, 0 rows affected (0.00 sec)

+---+--------+------------------------------------+
| k | projid | @name:=concat(@name,': ',projname) |
+---+--------+------------------------------------+
| 1 | 1 | : Bravo |
| 0 | 2 | : Bravo: Alhpa |
| 1 | 3 | : Bravo: Alhpa: Omega |
+---+--------+------------------------------------+
3 rows in set (0.00 sec)



--
Luuk

On Tue, 13 May 2008 19:34:19 +0200, mcl <mcl.office@googlemail.com> wrote:

> I would like to create an sql select that listed all group records as
> a concatenated string, but I just can not work out how to do it.
>
> eg Table1 Clothes Colours
> COLOUR
> red
> blue
> green
> yellow
>
> Table2 Clothes
> COLOUR ITEM
> red pullover
> green jumper
> green trousers
> yellow shirt
> red hat
> red scarf
> red coat
>
> I want to be able to create a Select with two fields COLOUR, ITEMs,
> which does the following, where ITEMS is concatenated from each
> matching group record in Table2 by COLOUR
> COLOUR ITEMS
> red pullover: hat: scarf: coat
> green jumper: trousers
> yellow shirt
>
>
> I hope I have explained it correctly.
>
> Any help or pointers, would be most appreciated, even if it is - NOT
> POSSIBLE


SELECT x.COLOUR GROUP_CONCAT(y.ITEM SEPARATOR ': ')
FROM ClothesColours x
LEFT JOIN Clothes y
ON y.COLOUR = x.COLOUR
GROUP BY x.COLOUR

Not very portable though, if I recall correctly GROUP_CONCAT() is
something just MySQL does.
--
Rik Wasmus
[SPAM] Now temporarily looking for some smaller PHP/MySQL projects/work to
fund a self developed bigger project, mail me at rik at rwasmus.nl. [/SPAM]