show null records 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                            |  
+---------+------------+--------------------+

I tried, ISNULL, IFNULL with a combination of group_by profile_answers.id which gave me the right result, but it turned out to be pretty expensive in terms of run time

http://dpaste.com/919899/ .. On this query it took 15 seconds — Manjunath Manoharan, Feb 11 '13 at 20:40
`"I tried, ISNULL..."` should simply have been a comment on [your previous question](http://stackoverflow.com/questions/14820298/include-null-results-in-group-concat). This should not have been a new question. — Bernhard Barker, Feb 11 '13 at 21:35

score 2 · Answer 1 · answered Feb 11 '13 at 20:37

This was my answer in your previous question...

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.

show null records in group_concat

1 Answers1