0

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:

enter image description here

enter image description here

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 ?

0

1 Answer 1

0

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()
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.