GROUP_CONCAT in SQLite

by

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;

More Related Contents:

  1. SQLite Order By Date1530019888000
  2. Select first row in each GROUP BY group?
  3. SQLite – UPSERT *not* INSERT or REPLACE
  4. How can I list the tables in a SQLite database file that was opened with ATTACH?
  5. Update table values from another table with the same user name
  6. SQL Select between dates
  7. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  8. What are valid table names in SQLite?
  9. FULL OUTER JOIN with SQLite
  10. Delete column from SQLite table
  11. sqlite database default time value ‘now’
  12. How do I insert datetime value into a SQLite database?
  13. SQLite – ORDER BY RAND()
  14. Select random row(s) in SQLite
  15. basic recursive query on sqlite3?
  16. SQLite and custom order by
  17. sqlite alter table add MULTIPLE columns in a single statement
  18. Drop all tables command
  19. SQLite auto-increment non-primary key field
  20. How to do IF NOT EXISTS in SQLite
  21. Core Data Performance with Single Parent Entity
  22. Match only entire words with LIKE?
  23. What is causing Foreign Key Mismatch error?
  24. How to create timestamp column with default value ‘now’?
  25. String concatenation does not work in SQLite
  26. How to split comma-separated values?
  27. Unfindable SQLite Syntax Error
  28. SQL – Inserting a row and returning primary key
  29. How to select the first N rows of each group?
  30. Copy table structure to new table in sqlite3

Leave a Comment