Mysql Join looping horizontal

Question

The first table of employee data

|ids| name     |
+=============+
|1 | Ibrahim  |
|2 | Rizky    |
|3 | David    |

The second table of people who come to work

|id| ids | date       |
+========+============+
|1 | 1   | 2018-05-01 |
|2 | 2   | 2018-05-01 |
|3 | 3   | 2018-05-01 |
|4 | 1   | 2018-05-02 |
|5 | 3   | 2018-05-02 |
|6 | 1   | 2018-05-03 |
|6 | 2   | 2018-05-03 |
|7 | 1   | 2018-05-04 |
|8 | 2   | 2018-05-04 |
|9 | 3   | 2018-05-04 |

the result I want, more or less like this

|name    | 2018-05-01 | 2018-05-02 | 2018-05-03 | 2018-05-04 |
=========+============+============+==-=========+============+
|Ibrahim |      1     |    1       |      1     |    1       |
|Rizky   |      1     |            |      1     |    1       |
|David   |      1     |    1       |            |    1       |

Can you help me, I want to display such data, the following date written is partial example, I want it one full month from the first to the end of the month. 1 means the person goes to work, empty means not coming to work.

can you help me with this, on how to query its join and how to display iteration in its appearance?

But that's not what you want to do here. Instead handle issues of data display in application code. — Strawberry, May 30 '18 at 05:40

jwood74 · Accepted Answer · 2018-05-30T06:57:40.710

There might be a fancy way to do it, but I would do it like this

select
 e.name,
 if(sum(if(w.date = '2018-05-01',1,0))=0,null,1) as '2018-05-01',
 if(sum(if(w.date = '2018-05-02',1,0))=0,null,1) as '2018-05-02',
 if(sum(if(w.date = '2018-05-03',1,0))=0,null,1) as '2018-05-03',
 if(sum(if(w.date = '2018-05-04',1,0))=0,null,1) as '2018-05-04',
 if(sum(if(w.date = '2018-05-05',1,0))=0,null,1) as '2018-05-05',
 if(sum(if(w.date = '2018-05-06',1,0))=0,null,1) as '2018-05-06',
 if(sum(if(w.date = '2018-05-07',1,0))=0,null,1) as '2018-05-07'
from employee e
left join work w ON e.ids = w.ids
group by e.ids

I group on employee ids, and use a sum(if( to determine if they worked that day or not.

Here is the dynamic method -

SET @s := (SELECT GROUP_CONCAT(DISTINCT CONCAT('if(sum(if(w.date = "',
                        date,
                        '",1,0))=0,null,1) AS `',
                        date,'`'
                        )
                    ) from work);
SET @s := concat('select e.name, ',@s,' from employee e 
         left join work w ON e.ids = w.ids
         group by e.ids');
PREPARE stmt FROM @s;
EXECUTE stmt;

Further information on the dynamic method available here.

this way it works for static data like that, but how to dynamic data, if I write 2018-05-01 date until 2018-05-31, well for next month how? do i have to rewrite 2018-06-01 to 2018-06-30? — Ifadak, May 30 '18 at 05:05
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from employee e left join work w ON e.ids = w.ids group' at line 1 -- there is an error in the query — Ifadak, May 30 '18 at 06:41

Mysql Join looping horizontal

1 Answers1