SQL statement to display in group of certain column

Question

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

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 and schedule_id (in block) is refer to which day it should be. Coloumn shift (in block) is defining block as morning (shift=1) and evening (shift=2),

I want to display something like this

date        day      Morning   Evening
22/09/2014  Monday   Ahmad     Faris
                     Abdul
23/09/2014  Tuesday            Iqbal

I have tried to create sql statement like

SELECT schedule.date, schedule.day, block.worker
FROM schedule 
LEFT JOIN block
ON schedule.id = block.schedule_id

but the 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 something missing in my statement, and how i to accomplish this.

Thanks for your help.

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

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 LEFT JOIN 
block bm ON s.id = bm.schedule_id AND bm.shift=1 LEFT JOIN 
block be ON s.id = be.schedule_id AND be.shift=2
GROUP BY 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 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 Morning
     FROM schedule s LEFT JOIN 
     block bm ON s.id = bm.schedule_id AND bm.shift=1
     GROUP BY s.date,s.day) T1
  JOIN
    (SELECT s.date, s.day,GROUP_CONCAT(be.worker SEPARATOR ' ') as Evening
     FROM schedule s LEFT JOIN 
     block be ON s.id = be.schedule_id AND be.shift=2
     GROUP BY s.date,s.day) T2 
  ON T1.Date=T2.Date AND 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 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('').

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 03 '14 at 05:31

Raging Bull

18,593
13
50
55

@iqbalplague: You can see the results in fiddle examples. – Raging Bull Jun 03 '14 at 05:43
how i display it in different row, is there any tag required ? (such as
) – iqbalplague Jun 03 '14 at 05:48
@iqbalplague: In different row? with other fields empty? That is not how SQL works. In such cases, I would prefer comma separated values. – Raging Bull Jun 03 '14 at 05:52
@RagingBull if your are going to use group concat approach then do brief regarding the consequences of group concat max length because this can create a problem for op in future if the names of workers are too long – M Khalid Junaid Jun 03 '14 at 05:52
1

actually i'll use worker_id instead of worker name and join them with other worker table. – iqbalplague Jun 03 '14 at 05:56
1

okay, i will try to look up for you answer. thanks for your guidance :) – iqbalplague Jun 03 '14 at 06:01
thank you very much :) actually i'am still newbie and much more to learn, and i appreciate it. – iqbalplague Jun 03 '14 at 06:04
@iqbalplague: Feel free to accept the answer if you are satisfied. – Raging Bull Jun 10 '15 at 09:19
@rene: Ah! You are right, my friend. I havn't flagged posts for a while. So I thought there is an option for spam in close vote. Almost forgot the spam button. Sorry about that. – Raging Bull Jun 10 '15 at 13:17

M Khalid Junaid · Answer 2 · 2014-06-17T06:38:42.207

1

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` = 2  
  ORDER BY 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 ($results as $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>

edited Jun 17 '14 at 06:38

answered Jun 03 '14 at 05:19

M Khalid Junaid

63,861
10
90
118

is there any statement needed like 'GROUP by', because i want to display only one date and day in a row containing multiple block.. (like my desired format) – iqbalplague Jun 03 '14 at 05:29
@iqbalplague see my last part of answer thats what i said for the repeated data you just need an order by date and in your application level code loop through records and in loop check or the repeated data and display only once – M Khalid Junaid Jun 03 '14 at 05:33
@iqbalplague see my answer to this question [*Combining Three tables into correct view format*](http://stackoverflow.com/questions/23243273/codeigniter-combining-three-tables-into-correct-view-format/23243935#23243935) how i have done loop to check the repeated data same logic you have o implement for above query,another answer [*php category, sub category tree*](http://stackoverflow.com/questions/23171673/php-category-sub-category-tree/23171864#23171864) – M Khalid Junaid Jun 03 '14 at 05:37
1

i see, so the repeated function is done in application level. Thanks for your guidance, i will try for it :) – iqbalplague Jun 03 '14 at 05:42
hi, i've been trying to use your answer, but it seems little problem. I've been using looping code in my application level, but the interface isn't appropiate. May i ask your guidance? – iqbalplague Jun 17 '14 at 04:22
thanks for your reply. I got same result as yours. but as you can see the "Faris" is printed twice, instead of one (since in database only one data). Is there any way to fix this? – iqbalplague Jun 17 '14 at 09:30

SQL statement to display in group of certain column

2 Answers2

Demo

Linked