one to many relations data fetch from database mysql

Question

This is my clockin table:

This is my user table:

I want to accepted output in this type:

Please publish sample data as text which we can use rather than images which we can't — P.Salmon, Dec 31 '22 at 08:42
I want full data of user table but group by date of clockin table — Sonali, Dec 31 '22 at 09:45
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Dec 31 '22 at 12:50
I have two tables first user and clockin , in user table 'saved all users data' , and clockin table user 'entry,break,leave' time stored. where I want to know the attendance of users through date — Sonali, Dec 31 '22 at 12:59
You need a calendar table or a way of generating dates for a date period eg https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query or a recursive cte https://stackoverflow.com/questions/67831719/recursive-cte-in-mysql-for-missing-dates then join and group concat, — P.Salmon, Dec 31 '22 at 14:16

Niyaz · Answer 1 · 2022-12-31T17:30:57.237

Try this:

set @start_date='2022-12-30';
set @end_date='2022-12-31';

WITH recursive Date_Ranges AS (
    select @start_date as date
    union all
    select date + interval 1 day from Date_Ranges
    where date < @end_date
)

SELECT
    new_table.*,
    (
        SELECT
            group_concat(`time`)
        FROM
            `clockin`
        WHERE
            `emid`=new_table.`id` AND
            `date`=new_table.`date`
    ) as `time`
FROM
    (select * from `user` full join Date_Ranges) as new_table
order by date desc

one to many relations data fetch from database mysql

1 Answers1