Include null results in group_concat

Question

I have two tables like this

profile_answers

+---------+------------+
|      id | class_name |
+---------+------------+
|       1 | Class 1    |
|       2 | Class 2    |
|       3 | Class 1    |
+---------+------------+

educations

+---------+--------------------+------------+
|      id | profile_answers_id |  sample    |
+---------+--------------------+------------+
|       1 | 1                  |     1234   |
|       2 | 1                  |     2334   |
|       3 | 1                  |     3434   |
+---------+------------+--------------------+

I ran the query,

select educations.profile_answer_id, GROUP_CONCAT(educations.sample) from educations
LEFT JOIN profile_answers ON educations.profile_answers_id = profile_answers.id

I got

+--------+--------------------+-------------+
|      id | sample                          | 
+---------+--------------------+------------+
|       1 | 1234,2334,3434                  |
+---------+------------+--------------------+

I actually want,

+--------+--------------------+-------------+
|      id | sample                          | 
+---------+--------------------+------------+
|       1 | 1234,2334,3434                  |
|       2 | NULL                            |
|       3 | NULL                            |  
+---------+------------+--------------------+

score 3 · Answer 1 · answered Feb 11 '13 at 20:57

3

SELECT id,IFNULL(samples,'NULL') sample FROM 
(
    SELECT
        AA.id,
        GROUP_CONCAT(DISTINCT BB.sample) samples
    FROM
        profile_answers AA LEFT JOIN educations BB
        ON AA.id = BB.profile_answers_id
    GROUP BY AA.id
) A;

answered Feb 11 '13 at 20:57

RolandoMySQLDBA

43,883
16
91
132

score 0 · Answer 2 · answered Feb 11 '13 at 20:33

0

Looks like you're missing your GROUP BY:

select profile_answers.id, GROUP_CONCAT(educations.sample) 
from profile_answers
LEFT JOIN educations ON educations.profile_answers_id = profile_answers.id
GROUP BY profile_answers.id

I also altered your JOIN to make the profile_answers table your main table.

Good luck.

answered Feb 11 '13 at 20:33

sgeddes

62,311
6
61
83

but then, adding group_by caused the execution time of this query very long. some where around 15 seconds for 4000 records – Manjunath Manoharan Feb 11 '13 at 20:37
@ManjunathManohar - You can try leaving it off, but I thought it was needed with GROUP_CONCAT. – sgeddes Feb 11 '13 at 20:38
1

leaving it off will give me just the one record – Manjunath Manoharan Feb 11 '13 at 20:40
2

@ManjunathManohar -- you do need GROUP BY -- see this example: http://sqlfiddle.com/#!2/5dc67/2 – sgeddes Feb 11 '13 at 20:41

Include null results in group_concat

2 Answers2

Linked

Related