Sql Statement To Display In Group Of Certain Column

I am currently at project of creating scheduling system based on web. I want to get data from database, and display it in table (schedule format). In database, I have 2 table sche

Solution 1:

Initially, this is what I came up with:

SELECT s.date, s.day, COALESCE(GROUP_CONCAT(bm.worker SEPARATOR ' '),'') as Morning,COALESCE(GROUP_CONCAT(be.worker SEPARATOR ' '),'') as Evening
FROM schedule s LEFTJOIN 
block bm ON s.id = bm.schedule_id AND bm.shift=1LEFTJOIN 
block be ON s.id = be.schedule_id AND be.shift=2GROUPBY s.date,s.day

Result:

DATEDAY       MORNING       EVENING
22/09/2014  Monday    Ahmad Abdul   Faris Faris
23/09/2014  Tuesday                 Iqbal

Result in Fiddle.

As you can see Evening field contains Faris twice. So I used two queries and joined those results. Like this:

SELECT T1.date,T1.day,COALESCE(T1.Morning,'') as Morning,COALESCE(T2.Evening,'') as Evening FROM
    (SELECT s.date, s.day, GROUP_CONCAT(bm.worker SEPARATOR ' ') as MorningFROM schedule s LEFT JOIN 
     block bm ON s.id = bm.schedule_id AND bm.shift=1GROUPBY s.date,s.day) T1
  JOIN
    (SELECT s.date, s.day,GROUP_CONCAT(be.worker SEPARATOR ' ') as EveningFROM schedule s LEFT JOIN 
     block be ON s.id = be.schedule_id AND be.shift=2GROUPBY s.date,s.day) T2 
  ON T1.Date=T2.DateAND T1.Day=T2.Day

Result:

DATEDAY      MORNING        EVENING
22/09/2014  Monday   Ahmad Abdul    Faris
23/09/2014  Tuesday                 Iqbal

See result in SQL Fiddle.

Explanation:

We are selecting for Morning and Evening separately, then we are joining these two tables with date and day. And finally result is fetched from the joined query.

GROUP_CONCAT is used to group fields having same date and day. We can use SEPARATOR ' ' for space as separator. If you remove SEPARATOR ' ', you will get the result seprated by comma(,).

COALESCE is used to replace null values with empty string('').

Solution 2:

You can get you desired result by joining your block table twice with separate shift filters

SELECT 
  s.date,
  s.day,
  b.worker Morning,
  bb.worker Evening 
FROM
  SCHEDULE s 
  LEFT JOIN block b 
    ON s.id = b.schedule_id 
    AND b.`shift` = 1 
  LEFT JOIN block bb 
    ON s.id = bb.schedule_id 
    AND bb.`shift` = 2ORDERBY s.date

Demo

Regarding the format you have shown it can be achievable from query by using user defined variable and for repeated data to show null,but this query will become so ugly and also optimization can't be guaranteed.Its better you do this in your application level code i.e php

Edit from comments

In php just use your logic to show date only once per group,fetch results from your query

$results =fetchfromquery(query);
$currentParent = false;
echo'<table>';
echo'<tr>
        <td>Date</td>
        <td>Day</td>
        <td>Morning</td>
        <td>Evening</td>
    </tr>';
foreach ($resultsas$r) {
    echo'<tr>';
    if ($currentParent != $r['date']) {
        echo'<td>' . $r['date'] . '</td>';
        $currentParent = $r['date'];
    }else{
        echo'<td>&nbsp;/td>';
    }
    echo'<td>' . $r['day'] . '</td>';
    echo'<td>' . $r['Morning'] . '</td>';
    echo'<td>' . $r['Evening'] . '</td>';
    echo'</tr>';
}
echo'</table>';

Above will output data in tabular format as below

<table>
    <tr><td>Date</td>       <td>Day</td>    <td>Morning</td>   <td>Evening</td></tr>
    <tr><td>22/09/2014</td> <td>Monday</td>  <td>Abdul</td>    <td>Faris</td></tr>
    <tr><td>          </td> <td>Monday</td>  <td>Ahmad</td>    <td>Faris</td></tr>
    <tr><td>23/09/2014</td> <td>Tuesday</td>  <td>      </td>    <td>Iqbal</td></tr>
</table>