php - SQL statement to display in group of certain column -

i @ project of creating scheduling system based on web. want data database, , display in table (schedule format).

in database, have 2 table

schedule id date        day 1  22/09/2014  monday 2  23/09/2014  tuesday

block block_id schedule_id   worker   shift 1         1            ahmad    1 2         1            abdul    1 3         1            faris    2 4         2            iqbal    2

each day of schedule has several block , schedule_id (in block) refer day should be. coloumn shift (in block) defining block morning (shift=1) , evening (shift=2),

i want display this

 date        day      morning   evening 22/09/2014  monday   ahmad     faris                      abdul 23/09/2014  tuesday            iqbal 

i have tried create sql statement like

 select schedule.date, schedule.day, block.worker schedule  left join block on schedule.id = block.schedule_id

but table isn't in appropiate format

 date        day      morning   evening 22/09/2014  monday   ahmad     22/09/2014  monday   abdul  22/09/2014  monday             faris 23/09/2014  tuesday            iqbal 

i know missing in statement, , how accomplish this.

thanks help.

initially, came with:

select s.date, s.day, coalesce(group_concat(bm.worker separator ' '),'') morning,coalesce(group_concat(be.worker separator ' '),'') evening schedule s left join  block bm on s.id = bm.schedule_id , bm.shift=1 left join  block on s.id = be.schedule_id , be.shift=2 group s.date,s.day 

result:

date        day       morning       evening 22/09/2014  monday    ahmad abdul   faris faris 23/09/2014  tuesday                 iqbal 

result in fiddle.

as can see evening field contains faris twice. used 2 queries , joined results. this:

select t1.date,t1.day,coalesce(t1.morning,'') morning,coalesce(t2.evening,'') evening     (select s.date, s.day, group_concat(bm.worker separator ' ') morning      schedule s left join       block bm on s.id = bm.schedule_id , bm.shift=1      group s.date,s.day) t1   join     (select s.date, s.day,group_concat(be.worker separator ' ') evening      schedule s left join       block on s.id = be.schedule_id , be.shift=2      group s.date,s.day) t2    on t1.date=t2.date , t1.day=t2.day 

result:

date        day      morning        evening 22/09/2014  monday   ahmad abdul    faris 23/09/2014  tuesday                 iqbal 

see result in sql fiddle.

explanation:

we selecting morning , evening separately, joining these 2 tables date , day. , result fetched joined query.

group_concat used group fields having same date , day. can use separator ' ' space separator. if remove separator ' ', result seprated comma(,).

coalesce used replace null values empty string('').