Group_concat In Sqlite

I am having data like this 1 A 1 B 1 C 1 D 2 E 2 F 3 G 3 H 3 I 3 J 3 K by using this query select ABSTRACTS_ITEM._id,Name from ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT

Solution 1:

You need to add GROUP BY clause when you are using aggregate function. Also use JOIN to join tables.

So try this:

SELECT AI._id, GROUP_CONCAT(Name) AS GroupedName
  FROM ABSTRACTS_ITEM AI 
  JOIN AUTHORS_ABSTRACT AAB ON AI.ID = AAB.ABSTRACTSITEM_ID
  JOIN ABSTRACT_AUTHOR AAU ON AAU._id = AAB.ABSTRACTAUTHOR_ID
 GROUPBY tbl._id;

See this sample SQLFiddle

---

What you were trying was almost correct. You just needed to add GROUP BY clause at the end. But the first one is better.

SELECT ID,
GROUP_CONCAT(NAME) 
FROM
    (select ABSTRACTS_ITEM._id AS ID,
     Name
     from
    ABSTRACTS_ITEM , ABSTRACT_AUTHOR , AUTHORS_ABSTRACT
    where
    ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
    and
    ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID)
GROUPBY ID;