GROUP_CONCAT on LEFT JOIN generates groups against NULL

Question

I'm trying to use a LEFT JOIN in conjunction with a GROUP_CONCAT but not getting the expected results.

Two simple tables:

weather_alerts:

id | user_id | resort_id
1  | 1       | 1
2  | 1       | 2
3  | 1       | 3
4  | 1       | 5

weather_users

id | email
1  | me@me.com

The query:

SELECT GROUP_CONCAT(wa.resort_id) AS resort_ids, wu.email FROM weather_alerts wa LEFT JOIN weather_users wu ON wa.id = wu.user_id GROUP BY wu.email

Instead of generating:

email            resort_ids
me@me.com        1,2,3,5

I get:

email            resort_ids
NULL             2,3,5
me@me.com        1

I suspect this is an issue with the JOIN rather than the CONCAT.

Answer 1

It appears that your LEFT JOIN needs improvement.

create table weather_alerts (id int, user_id int, resort_id int);
insert into weather_alerts values (1, 1, 1), (2, 1, 2), (3, 1, 3), (4, 1, 5);

create table weather_users (id int, email varchar(100));
insert into weather_users values (1, 'me@me.com');

Query

SELECT GROUP_CONCAT(wa.resort_id ORDER BY wa.resort_id) AS resort_ids, wu.email 
FROM weather_alerts wa 
LEFT JOIN weather_users wu ON wa.user_id = wu.id 
GROUP BY wu.email

Notice that you are joining on wa.id = wu.user_id. The join should be on wa.user_id = wu.id

Result

| resort_ids |     email |
|------------|-----------|
|    1,2,3,5 | me@me.com |