How return list with inner list in SQL?

All we need is an easy explanation of the problem, so here it is.

I prepared following SQL statement:

SELECT tasks.name, profiles.email, types.specification,
    tasks.description, tasks.attachment_link, tasks.priority,
    assigned_tasks.progress_details, assigned_tasks.activation_date,
    assigned_tasks.expired_date  
FROM profiles, types, tasks, assigned_tasks 
WHERE profiles.id = assigned_tasks.profile_id 
AND assigned_tasks.task_id = tasks.id 
AND types.id = tasks.type_id
ORDER BY tasks.name;

I want show in view gruped tasks with assigned email. Something like this:

How return list with inner list in SQL?

How return list with inner list in SQL?

I see records with single assignments, task names are duplicated. I want to show e-mail list in owner cell in table – it is possible?

Python return touples:

('Backend', '[email protected]', 'Database managment', 'Create serwer full backend and copy data to SSD 1TB', 'NULL', 2, 'to do - 0% progress', datetime.datetime(2021, 6, 1, 10, 18, 36), None)
('call to customer', '[email protected]', 'IT online support', 'answer for questions about cloud', 'https://dianet.pl/cloud/index.html', 1, 'TO DO', datetime.datetime(2021, 6, 1, 14, 57, 23), None)

I want something like that:

('Backend', ('[email protected]', '[email protected]'), 'Database managment', 'Create serwer full backend and copy data to SSD 1TB', 'NULL', 2, 'to do - 0% progress', datetime.datetime(2021, 6, 1, 10, 18, 36), None)

Two users might be assign to one task I want show it in HTML table. How split records ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Use GROUP_CONCAT.
Use the JOIN .. ON syntax instead of the old syntax.

SELECT  tasks.name,
        GROUP_CONCAT(profiles.email) AS emails,
        types.specification,
        tasks.description, tasks.attachment_link, tasks.priority,
        ats.progress_details, ats.activation_date, ats.expired_date  
    FROM profiles AS p
    JOIN assigned_tasks AS ats  ON ats.profile_id = p.id
    JOIN tasks  ON tasks.id = ats.task_id
    JOIN types  ON types.id = tasks.type_id
    GROUP BY tasks.name,
             types.specification,
             tasks.description, tasks.attachment_link, tasks.priority,
             ats.progress_details, ats.activation_date, ats.expired_date  
    ORDER BY tasks.name;

Instead of datetime.datetime(2021, 6, 1, 10, 18, 36), simply use

"2021-06-01 10:18:26"

Or you could use this, if you simply want the current date and time:

NOW()

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply