How to GROUP_CONCAT from a SELECT statement?

[David_Cox]

I have an SQLite database with two tables, Documents and Versions. They’re linked in that the Versions table has a DocumentID AS Integer field to link the document version to the Documents table.

Displaying each in a ListBox is easy, as is adding extra fields from the other table eg
SELECT id, VersionNumber, DocumentID, (SELECT Name FROM Documents WHERE id = Versions.DocumentID) AS Name FROM Versions

My problem is how to extract the multiple VersionNumber’s when building a RowSet from the Documents table perspective. If I try this I only get one VersionNumber (there are many versions):

SELECT id, Name, (SELECT VersionNumber FROM Versions WHERE DocumentID = Documents.id) AS VersionNumbers FROM Documents

So I thought I could use the GROUP_CONCAT command to return a list of comma-separated VersionNumbers. If I try this I get an SQL error near SELECT:
SELECT id, Name, GROUP_CONCAT(SELECT VersionNumber FROM Versions WHERE DocumentID = Documents.id) AS VersionNumbers FROM Documents

It seems like GROUP_CONCAT can’t operate on a SELECT… command.

How can I get the VersionNumbers return as a SELECT Field?

[Sascha_S]

You can’t do a SELECT within a GROUP_CONCAT, you will need to JOIN Versions and Documents.

Try this instead:

SELECT Documents.id, Documents.Name, 
GROUP_CONCAT(Versions.VersionNumber) AS VersionNumbers
FROM Documents
LEFT JOIN Versions ON Versions.DocumentID = Documents.id
GROUP BY Documents.id

BTW: LEFT JOIN makes sure you also get Documents w/o a Version

[David_Cox]

Thank you @Sascha_S!

Your way is best, but I was also able to solve it with (plus added space after the comma and set the ORDER BY):
SELECT id, Name, (SELECT GROUP_CONCAT(VersionNumber, ', ') FROM Versions WHERE DocumentID = Documents.id ORDER BY VersionNumber) AS VersionNumbers FROM Documents

Here is the result

[Sascha_S]

The main thing is that it works. It works flawlessly and as quickly as necessary.

[Richard_Duke]

I don’t think you need to LEFT JOIN to another table. In the following code I have a query for tblItem and I was doing a group by and order by category and then showing all the RecordNumbers belong to the same category

WITH 
qry_CTE1
(
Ownership, PropertyName, Category, RecordNumber
)
AS 
(
SELECT Ownership, PropertyName, Category, RecordNumber
FROM tblItem 
WHERE DeleteYN=0
[**WHEREPART1**]
),
qry_CTE2
(
Category, Category, Count, GrpID 
)
AS 
(
SELECT Category, Category, COUNT(RecordNumber) as Cnt, GROUP_CONCAT(RecordNumber) As GrpID 
FROM qry_CTE1  
GROUP BY Category
ORDER BY Category
)
SELECT * 
FROM qry_CTE2
[**WHEREPART2**]