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
where
ABSTRACTS_ITEM._id = AUTHORS_ABSTRACT.ABSTRACTSITEM_ID
and
ABSTRACT_AUTHOR._id = AUTHORS_ABSTRACT.ABSTRACTAUTHOR_ID

Now, I want to show data like this

1 A,B,C,D
2 EF

and so on..I also know it can achieve by GROUP_CONCAT function. So, I tried with this

SELECT ABSTRACTS_ITEM._id,
GROUP_CONCAT(ABSTRACT_AUTHOR.NAME) FROM
(select ABSTRACTS_ITEM._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)

But, It shows me error. So, what I am doing wrong here. What are the right procedure to achieve that?

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
 GROUP BY 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)
GROUP BY ID;