GROUP_CONCAT in a joined table

[bostboy]

I have two simple tables, the first is usr_accounts with accountid and activethru (datetime) and usr_users with userid, accountid and fullname.

I want to get a query by activethru date that gives me a count of accounts expiring on the day and also a comma separated list of the names of those people.

I have tried pretty much every combination that I can think of and the closest is

SELECT date(u1.activethru), count(*) as Expiring,
    (select GROUP_CONCAT(u2.fullname) from usr_users as u2 where u2.accountid = u1.accountid group by u1.activethru) as person
    FROM usr_accounts as u1
    GROUP BY date(u1.activethru) ORDER BY date(u1.activethru)

This gives me the correct count but in the person field it shows NULL if there is more than one account expiring on that day. If there is only one, it gives me the person’s name correctly.

Thanks

[r937]

SELECT DATE(u1.activethru) AS active_date , COUNT(*) AS Expiring , GROUP_CONCAT(u2.fullname) AS persons FROM usr_accounts as u1 INNER JOIN usr_users as u2 ON u2.accountid = u1.accountid GROUP BY DATE(u1.activethru) ORDER BY DATE(u1.activethru)

[bostboy]

Thanks for the response. When I looked at it I thought I’ve tried that. So I ran it and it returned the same problem I was seeing before. So I checked the tables again, which I thought I had done before, and lo and behold, I have a data problem. Anyhow, my bad. Sorry to bother you and again, thanks for the input. I knew you wouldn’t be wrong so it had to be on my side.