I have tables(mysql 5.5 INNODB):sample small data + cut not relevant columns for questions - real data huge(more then 10,000,000 rows in meeting table)
INPUT:
day:
day_id(primary key)|sunday|monday|tuesday|wensday|thursday|friday|saturday
---------------------------------------------------------------------------
60 |1 |1 |1 |1 |1 |1 |1
74 |0 |1 |0 |0 |0 |0 |0
98 |0 |0 |0 |0 |0 |0 |0
..etc......................................................................
meeting:
day_id(foreign key)|time |type
----------------------------------
60 |20:09:00|1
98 |07:00:00|1
74 |22:01:00|4
98 |10:00:00|1
60 |23:09:00|2
98 |06:00:00|2
74 |21:04:00|6
98 |08:00:00|6
.....etc.....................
OUTPUT:
sunday_times |monday_times |....etc
-------------------------------------------------------------------------------------------------
20:09:00,etc... | 20:09:00,etc.. |
................etc..............................................................................
I think something like that (but I am new with sql soo i don't know if it good or legal)
SELECT
GROUP_CONCAT(time where day.sunday=1 ORDER BY time) as sunday_times,
GROUP_CONCAT(time where day.monday=1 ORDER BY time) as monday_times,
etc..
FROM day
JOIN meeting ON meeting.day_id=day.day_id
GROUP BY type
Your syntax was close, you need to use a CASE statement inside of the GROUP_CONCAT():
SELECT
GROUP_CONCAT(case when day.sunday=1 then time end order by time) as sunday_times,
GROUP_CONCAT(case when day.monday=1 then time end order by time) as monday_times
FROM day
JOIN meeting ON meeting.day_id=day.day_id
GROUP BY type