When using GROUP_CONCAT, the ORDER BY clause appears to works correctly. However, when replacing NULL values using ISNULL or COALESCE, although the actual values are in order, the NULL values all end up at the beginning of the concatenation (see below). How do I get my NULL replacement values to be in the correct column order so they properly represent the values being replaced.
SELECT filledForm.creationDate AS date, GROUP_CONCAT(IFNULL(answers.answer, "NULL") ORDER BY answers.promptID) AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
GROUP BY filledForm.filledFormID;
using some sample data this gives:
+---------------------+-----------------------------------------------------------------+
| date | answers |
+---------------------+-----------------------------------------------------------------+
| 2023-03-10 14:21:03 | NULL,NULL,NULL,NULL,NULL,bob,2023-03-16,English |
| 2023-03-11 02:28:23 | NULL,NULL,NULL,John Smith ,60,test2@get.com,2023-03-24,German |
| 2023-03-11 02:32:41 | NULL,NULL,Tom,22,tom@testURL.com,2023-03-29,11:00,English |
+---------------------+-----------------------------------------------------------------+
instead of:
+---------------------+-----------------------------------------------------------------+
| date | answers |
+---------------------+-----------------------------------------------------------------+
| 2023-03-10 14:21:03 | bob,NULL,NULL,2023-03-16,NULL,English,NULL,NULL |
| 2023-03-11 02:28:23 | John Smith,60,test2@get.com,2023-03-24,NULL,German,NULL,NULL |
| 2023-03-11 02:32:41 | Tom,22,tom@testURL.com,2023-03-29,11:00,EnglishNULL,NULL |
+---------------------+-----------------------------------------------------------------+
which can be seen when I run the query without the group by portion:
SELECT answers.answer AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
ORDER BY filledForm.filledFormID, prompts.promptID;
+-------------------+
| answers |
+-------------------+
| bob |
| NULL |
| NULL |
| 2023-03-16 |
| NULL |
| English |
| NULL |
| NULL |
| John Smith |
| 60 |
| test2@get.com |
| 2023-03-24 |
| NULL |
| German |
| NULL |
| NULL |
| Tom |
| 22 |
| tom@testURL.com |
| 2023-03-29 |
| 11:00 |
| English |
| NULL |
| NULL |
+-------------------+
or perhaps more easily understood with a few more columns showing:
SELECT filledForm.filledFormID, prompts.promptID, answers.answer AS answers
FROM filledForm
JOIN prompts ON filledForm.formID = prompts.formID AND prompts.formID = 100
LEFT JOIN answers ON prompts.promptID = answers.promptID AND filledForm.filledFormID = answers.filledFormID
ORDER BY filledForm.filledFormID, prompts.promptID;
+--------------+----------+-------------------+
| filledFormID | promptID | answer |
+--------------+----------+-------------------+
| 14 | 9 | bob |
| 14 | 10 | NULL |
| 14 | 11 | NULL |
| 14 | 12 | 2023-03-16 |
| 14 | 13 | NULL |
| 14 | 14 | English |
| 14 | 15 | NULL |
| 14 | 16 | NULL |
| 15 | 9 | John Smith |
| 15 | 10 | 60 |
| 15 | 11 | test2@get.com |
| 15 | 12 | 2023-03-24 |
| 15 | 13 | NULL |
| 15 | 14 | German |
| 15 | 15 | NULL |
| 15 | 16 | NULL |
| 16 | 9 | Tom |
| 16 | 10 | 22 |
| 16 | 11 | tom@testURL.com |
| 16 | 12 | 2023-03-29 |
| 16 | 13 | 11:00 |
| 16 | 14 | English |
| 16 | 15 | NULL |
| 16 | 16 | NULL |
+--------------+----------+-------------------+
These are the full tables:
CREATE TABLE prompts (
promptID INT PRIMARY KEY AUTO_INCREMENT,
formID INT NOT NULL,
FOREIGN KEY (formID)
REFERENCES form (formID)
ON DELETE CASCADE,
hash VARCHAR(16) NOT NULL,
prompt TEXT(1023) NOT NULL,
changeDate TIMESTAMP
UNIQUE(formID, hash)
);
CREATE TABLE filledForm (
filledFormID INT PRIMARY KEY AUTO_INCREMENT,
formID INT NOT NULL,
FOREIGN KEY (formID)
REFERENCES form (formID)
ON DELETE CASCADE,
creationDate TIMESTAMP NOT NULL DEFAULT (UTC_TIMESTAMP),
INDEX (formID)
);
CREATE TABLE answers (
id INT PRIMARY KEY AUTO_INCREMENT,
filledFormID INT NOT NULL,
FOREIGN KEY (filledFormID)
REFERENCES filledForm (filledFormID)
ON DELETE CASCADE,
promptID INT NOT NULL,
FOREIGN KEY (promptID)
REFERENCES prompts (promptID)
ON DELETE CASCADE,
answer TEXT(99999),
INDEX (filledFormID)
);
SET SESSION group_concat_max_len = 1000000;
Any help would be appreciated.
You are trying to ORDER BY answers.promptID
which is on the righthand side of your LEFT JOIN
. ORDER BY prompts.promptID
instead.
SELECT
f.creationDate AS date,
GROUP_CONCAT(IFNULL(a.answer, "NULL") ORDER BY p.promptID) AS answers
FROM filledForm f
JOIN prompts p ON f.formID = p.formID AND p.formID = 100
LEFT JOIN answers a ON p.promptID = a.promptID AND f.filledFormID = a.filledFormID
GROUP BY f.filledFormID;