SQL GROUP_CONCAT in subquery does not give the desired data-CodePudding

im trying to select data with a subquery. If i run the subquery without the other stuff around it works as expected, otherwise it returns only data from 1 row and not 4.

SELECT t.IDS,
(
    SELECT GROUP_CONCAT(_t.DATA SEPARATOR ';')
    FROM table _t
    WHERE _t.ID IN (t.IDS)
) as DATA
FROM (
    SELECT '1,2,3,4' as IDS
) t;

Table:

ID	DATA
1	Test
2	Test1
3	Test2
4	Test3

The query should return this: IDS: '1,2,3,4' DATA: 'Test;Test1;Test2;Test3'

Currently it returns this: IDS: '1,2,3,4' DATA: 'Test'

The query above is only an example.

CodePudding user response：

Your table t is a string so that IN doesn't work, but you can use FIND_IN_SETinstead, but the peromance is bad.

CREATE TABLE tab1 (
  `ID` INTEGER,
  `DATA` VARCHAR(5)
);

INSERT INTO tab1
  (`ID`, `DATA`)
VALUES
  ('1', 'Test'),
  ('2', 'Test1'),
  ('3', 'Test2'),
  ('4', 'Test3');

SELECT t.IDS,
(
    SELECT GROUP_CONCAT(_t.DATA  ORDER BY _t.ID SEPARATOR ';')
    FROM tab1 _t
    WHERE FIND_IN_SET(_t.ID,t.IDS)
) as DATA
FROM (
    SELECT '1,2,3,4' as IDS
) t;

IDS     | DATA                  
:------ | :---------------------
1,2,3,4 | Test;Test1;Test2;Test3

db<>fiddle here