user2168066 user2168066 - 2 months ago 19
MySQL Question

Group Concat from a select statement

I am not sure if my method is possible, but i'm trying to do a group_concat on a select statement that concats 2 fields. I get the error: Subquery returns more than 1 row each time. Can anyone help me as to a solution, or better way around this.

select t.recnum, (select group_concat((select concat(b.origtests,'^', d.name) as testing
from order_origtests b
join profile c on c.code = b.origtests
join department d on d.recnum = c.dept
)))
FROM order_ t

Answer

You don't put SELECT inside GROUP_CONCAT. It should be

select t.recnum, (
    select group_concat(concat(b.origtests,'^', d.name))
    from order_origtests b
    join profile c on c.code = b.origtests
    join department d on d.recnum = c.dept
    ) AS testing
FROM order_ t

Note that your subquery isn't correlated to anything in t, so you'll get the same testing column for every recnum.

Comments