Value return incorrectly after calculated field

Question

SELECT
    t3.id,
    t3.prod_ID,
    MIN(diff) AS min_time
FROM
    (SELECT
         t1.id,
         (UNIX_TIMESTAMP(t2.time_stamp_2) - UNIX_TIMESTAMP(t1.time_stamp)) AS diff
     FROM 
         production t1
     LEFT JOIN 
         process t2 ON t1.id = t2.id
     HAVING 
         diff >= 0) tx
LEFT JOIN 
    production t3 ON t3.id = tx.id
GROUP BY
    t3.id

After run, the returned result is:

id  prod_ID   min_time
-----------------------
1   2         1200

What it should return instead is

id  prod_ID   min_time
1   9         1200

I initially thought there was an error in joining, after multiple join test, same error result.

SQLFiddle

SQLFiddle_2

SQLFiddle_3

SQLFiddle_2 to clarify that I use Group By because I have multiple ID

SQLFiddle_3 to expand more.

After run on SQLFiddle_3, the returned result is:

id  prod_ID   min_time
-----------------------
1   2         1200
2   2         960
3   2         360

What it should be is

id  prod_ID   min_time
-----------------------
1   9         1200
2   2         960
3   3         360

Answer 1

In your query you GROUP BY t3.id only and you don't aggregate on t3.prod_ID, so the value returned is undefined.

I believe that you don't need to GROUP BY, or even rejoin to production.
Try this:

SELECT t1.id, t1.prod_ID,
       (UNIX_TIMESTAMP(t2.time_stamp_2) - UNIX_TIMESTAMP(t1.time_stamp)) AS diff
FROM production t1 INNER JOIN process t2
ON t1.id = t2.id 
AND (t1.id, t1.prod_ID) = (
  SELECT p.id, p.prod_ID
  FROM production p
  WHERE p.id = t2.id AND (UNIX_TIMESTAMP(t2.time_stamp_2) - UNIX_TIMESTAMP(p.time_stamp)) >= 0
  ORDER BY (UNIX_TIMESTAMP(t2.time_stamp_2) - UNIX_TIMESTAMP(p.time_stamp)) LIMIT 1
)

See the demo.
Results:

> id | prod_ID | diff
> -: | ------: | ---:
>  1 |       9 | 1200
>  2 |       2 |  960