Sorry for the repost but this is a question I got most of the answer to a month ago. But still haven't got it completely working the way I want.
Have a date field that I am getting out of my DB with the following;
SELECT event,event_name,
GROUP_CONCAT(DATE_FORMAT(ed.date,'%b %e') ORDER BY ed.date SEPARATOR ',') as date2
FROM events ev
LEFT JOIN event_dates ed ON ev.eid=ed.eid
WHERE ev.yr='2013'
GROUP BY ev.eid
This produces this list of dates that takes up way to much space.
May 16,May 23,May 30,Jun 6,Jun 20
I want it to look like
May 16,23,30,Jun 6,20
Here is the query I tried based on the input from last time. but it doesn't work because the sub-query doesn't seem to know the event id. guessing i might need to convert it to a join??
SELECT DISTINCT ev.event_id,
GROUP_CONCAT(DISTINCT DATE_FORMAT(race_date,'%a') ORDER BY race_date
SEPARATOR ', ') as date1,
(SELECT GROUP_CONCAT(dm) FROM
(SELECT CONCAT(
MONTHNAME(race_date),
' ',
GROUP_CONCAT(DAY(race_date) ORDER BY race_date)
) as dm
FROM event_dates ed1 WHERE ed1.event_id=ev.event_id
GROUP BY MONTH(race_date))as t) as date2
FROM events ev
JOIN event_dates ed ON ev.event_id = ed.event_id AND ev.race_year = ed.race_year
GROUP BY event_id
This should get you pretty close to what you're looking for:
SELECT event_name, date2
FROM (
SELECT
@showMonth:=IF(CONCAT(MONTHNAME(ed.date),ev.eid)=@prevMonth,'',CONCAT(MONTHNAME(ed.date), ' ')),
event_name,
MONTHNAME(ed.date),
GROUP_CONCAT(CONCAT(@showMonth, DATE_FORMAT(ed.date,'%e')) ORDER BY ed.date SEPARATOR ',') as date2,
@prevMonth:=CONCAT(MONTHNAME(ed.date),ev.eid)
FROM events ev
LEFT JOIN event_dates ed ON ev.eid=ed.eid
JOIN (SELECT @showMonth:='', @prevMonth:='') r
WHERE ev.yr=2013
GROUP BY ev.eid
) t