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 | [email protected]
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
[email protected] 1,2,3,5
I get:
email resort_ids
NULL 2,3,5
[email protected] 1
I suspect this is an issue with the JOIN rather than the CONCAT.
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, '[email protected]');
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 | [email protected] |