How to get multiple rows/columns matched from another table in mysql

Question

I have a records table and a history table that adds timestamps as records flow through our system. They look something like this...

records
+------+-----------+----+
|   id | name      | ...|
+------+-----------+----+
|    1 | host1     | ...|
|    2 | host2     | ...|
|    3 | host3     | ...|
|    4 | host4     | ...|
+------+-----------+----+

history
+----+-----------+------------+--------+--------+
| id | record_id | timestamp  | module | status |
+----+-----------+------------+--------+--------+
|  5 | 1         | 2019-01-01 | eng    | new    |
|  6 | 1         | 2019-01-03 | eng    | done   |
|  7 | 2         | 2019-01-01 | eng    | new    |
|  8 | 2         | 2019-01-04 | eng    | done   |
|  9 | 3         | 2019-01-02 | eng    | new    |
+----+-----------+------------+--------+--------+

This is greatly simplified as the history table may or may not contain many different modules and status entries depending on record workflow. What I need is a list of records along with a timestamp for NEW and DONE if they exist or null if not, like this...

+------+-----------+----------------+---------------+
|   id | name      | eng_start_time |  eng_end_time |
+------+-----------+----------------+---------------+
|    1 | host1     | 2019-01-01     | 2019-01-03    |
|    2 | host2     | 2019-01-01     | 2019-01-04    |
|    3 | host3     | 2019-01-02     | null          |
|    4 | host4     | null           | null          |
+------+-----------+----------------+---------------+

I was able to get these results by using 2 subqueries, but I fear this might not be efficient as these tables grow very large. Here's my query that works. Is there a way to get these results with a more efficient join/subselect?

select r.id, r.name, 
  (select timestamp from history where request_id = r.id and module="eng" and status="new") as eng_start_time,
  (select timestamp from history where request_id = r.id and module="eng" and status="done") as eng_end_time
  from records r order by r.id

Answer 1

You could achieve this with two left JOINs.

select r.id, r.name, h_start.timestamp eng_start_time, h_end.timestamp eng_end_time
from records r
left join history h_start 
    ON h_start.request_id = r.id AND h_start.module='eng' AND h_start.status='new'
left join history h_end
    ON h_end.request_id = r.id AND h_end.module='eng' AND h_end.status='done'
order by r.id

NB : I replaced your double quotes with singles, that is a better practice in SQL.

Answer 2

Looks like you need some LEFT JOINs:

select r.id, r.name, hs.timestamp as eng_start_time, he.timestamp as eng_end_time
  from records r
  left join history hs on r.id = hs.request_id and hs.module="eng" and hs.status="new"
  left join history he on r.id = he.request_id and he.module="eng" and he.status="done"
  order by r.id