Skip to content Skip to sidebar Skip to footer

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;

Post a Comment for "Group_concat In Sqlite"