Hi,

I'm trying to do a group_concat() on the result of a join but I'm
lacking a bit of experience to make it work.

I have 3 tables:

-star_sample_transform
-star_sample_transform_group
-star_sample_transform_name

star_sample_transform contains id's which represent names from
star_sample_transform_name, so I use joins to represent the data.

What I would like to see is this:

| name |
-----------
name1, name2, name3

What I get when doing the following:

SELECT t3.transform_name_name
FROM `star_sample_transform` AS t1
LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
= t2.transform_group_id
LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
t3.transform_name_id
WHERE t1.old_sample_id = '1'
GROUP BY t2.transform_group_id

| name |
-----------
name1
name2
name3

This is good, but I would like to have this result in one row and one
column to be used in a subquery.

When I try a GROUP_CONCAT on my result I get this:

SELECT GROUP_CONCAT( t3.transform_name_name)
FROM `star_sample_transform` AS t1
LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
= t2.transform_group_id
LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
t3.transform_name_id
WHERE t1.old_sample_id = '1'
GROUP BY t2.transform_group_id

| name |
-----------
name1, name1, name1
name2, name2, name2, name2, name2, name2
name3

When I change the GROUP_BY the result changes but not as wanted.

SELECT GROUP_CONCAT( t3.transform_name_name)
FROM `star_sample_transform` AS t1
LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
= t2.transform_group_id
LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
t3.transform_name_id
WHERE t1.old_sample_id = '1'
GROUP BY t1.old_sample_id

The result looks the following:

| name |
-----------
name1, name1, name1, name2, name2, name2, name2, name2, name2, name3

Using a DISTINCT in my GROUP_CONCAT doesn't because name1 can be
the same as name2.


SELECT GROUP_CONCAT( DISTINCT t3.transform_name_name)
FROM `star_sample_transform` AS t1
LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
= t2.transform_group_id
LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
t3.transform_name_id
WHERE t1.old_sample_id = '1'
GROUP BY t1.old_sample_id

| name |
-----------
name1, name3

I'm using Mysql 4.1.20. Can someone me a bit further?

On Dec 31, 5:16 pm, pieter.th...@gmail.com wrote:
> Hi,
>
> I'm trying to do a group_concat() on the result of a join but I'm
> lacking a bit of experience to make it work.
>
> I have 3 tables:
>
> -star_sample_transform
> -star_sample_transform_group
> -star_sample_transform_name
>
> star_sample_transform contains id's which represent names from
> star_sample_transform_name, so I use joins to represent the data.
>
> What I would like to see is this:
>
> | name |
> -----------
> name1, name2, name3
>
> What I get when doing the following:
>
> SELECT t3.transform_name_name
> FROM `star_sample_transform` AS t1
> LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
> = t2.transform_group_id
> LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
> t3.transform_name_id
> WHERE t1.old_sample_id = '1'
> GROUP BY t2.transform_group_id
>
> | name |
> -----------
> name1
> name2
> name3
>
> This is good, but I would like to have this result in one row and one
> column to be used in a subquery.
>
> When I try a GROUP_CONCAT on my result I get this:
>
> SELECT GROUP_CONCAT( t3.transform_name_name)
> FROM `star_sample_transform` AS t1
> LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
> = t2.transform_group_id
> LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
> t3.transform_name_id
> WHERE t1.old_sample_id = '1'
> GROUP BY t2.transform_group_id
>
> | name |
> -----------
> name1, name1, name1
> name2, name2, name2, name2, name2, name2
> name3
>
> When I change the GROUP_BY the result changes but not as wanted.
>
> SELECT GROUP_CONCAT( t3.transform_name_name)
> FROM `star_sample_transform` AS t1
> LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
> = t2.transform_group_id
> LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
> t3.transform_name_id
> WHERE t1.old_sample_id = '1'
> GROUP BY t1.old_sample_id
>
> The result looks the following:
>
> | name |
> -----------
> name1, name1, name1, name2, name2, name2, name2, name2, name2, name3
>
> Using a DISTINCT in my GROUP_CONCAT doesn't because name1 can be
> the same as name2.
>
> SELECT GROUP_CONCAT( DISTINCT t3.transform_name_name)
> FROM `star_sample_transform` AS t1
> LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
> = t2.transform_group_id
> LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
> t3.transform_name_id
> WHERE t1.old_sample_id = '1'
> GROUP BY t1.old_sample_id
>
> | name |
> -----------
> name1, name3
>
> I'm using Mysql 4.1.20. Can someone me a bit further?

Solved it myself. Apparently, you can use a subquery to select FROM
(SELECT ...) AS x:

SELECT GROUP_CONCAT(x.transform_name_name) FROM ((SELECT
t1.transform_id, t3.transform_name_name, t1.old_sample_id
FROM `star_sample_transform` AS t1
LEFT JOIN `star_sample_transform_group` AS t2 ON t1.transform_group_id
= t2.transform_group_id
LEFT JOIN `star_sample_transform_name` AS t3 ON t2.transform_name_id =
t3.transform_name_id
WHERE t1.old_sample_id = '1'
GROUP BY t2.transform_group_id)) AS x GROUP BY x.old_sample_id