0

I have a database, holding workers and three different tasks they can complete. Each task table holds which worker carried out this task, when, and how long it took.

So the tables look as follows:

Worker table:

-------------------------
|worker_id | worker_name|
|----------|------------|
|          |            |

Task tables:

------------------------------------
|task_id | worker_id | date | time |
|--------|-----------|------|------|
|        |           |      |      |

Each task table looks as above, called task1, task2, task3. It is not possible to have these in the same table as each task has other columns specific to itself which are not required for this union, but are used elsewhere.

The union of the three task tables to be used is as follows:

SELECT `date`, time FROM task1
UNION ALL SELECT `date`, time FROM task2
UNION ALL SELECT `date`, time FROM task3

How would one go about writing a select statement, using the above union, to return each workers id and name, along with the date and length of time of their most recent task.

I have tried using the chosen answer from the following question, but replacing the wp_posts table with the union of the three task tables, with no success: MySQL order by before group by

Community
  • 1
  • 1
user4370541
  • 9
  • 1
  • 4

1 Answers1

0

I'm going to recommend the substring_index()/group_concat() trick:

SELECT userid, max(date) as date,
       substring_index(group_concat(time order by date desc), ',', 1) as time
FROM (SELECT userid, `date`, time FROM task1 UNION ALL
      SELECT userid, `date`, time FROM task2 UNION ALL
      SELECT userid, `date`, time FROM task3
     ) u
GROUP BY userid;

This converts time to a string. You might want to use date_format() if you want it in a particular format.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786