how to relate two mysql tables that are not directly connected through a key

Question

I have the following two mysql tables.

Table Name: Analysis

id   execution_time
---  --------------
A1   1
A2   20
A3   35

Table Name: Bugs

bug_id creation_time
------ -------------
1000   1.1
1001   1.3
1002   20.2
1003   20.7
1004   20.9
1005   35.1

Bugs 1000, 1001 are created for the Analysis A1

Bugs 1002, 1003, 1004 are created for Analysis A2

Bug 1005 is created for Analysis A3

So, Bug creation_time is always greater than the execution_time of its Analysis and bug_creation_time is always less than the execution of the later analyses.

Now, 1) how can I find the related bugs (bug_id) given that I have an analysis id.

2) how can I find the related analysis id for a given bug data (id, creation_time)

Answer 1

1) SQL FIDDLE DEMO

SELECT b.*, t.*
FROM Bugs b
CROSS JOIN (  SELECT a1.id, 
                     a1.`execution_time` as `start_time`, 
                     a2.`execution_time` as `end_time`
              FROM Analysis a1
              LEFT JOIN Analysis a2
                 ON a1.`execution_time` < a2.`execution_time`  
              WHERE a2.`execution_time` IS NULL
                 OR a2.`execution_time`  = (SELECT min( z.`execution_time`)
                                            FROM Analysis z
                                            WHERE z.`execution_time` > a1.`execution_time`)
            ) t
WHERE b.`creation_time` between `start_time` and `end_time`
   or (b.`creation_time` > `start_time` and `end_time` IS NULL)

OUTPUT: only need filter by id

2)

SELECT *
FROM Analysis a
WHERE a.id = (  SELECT MAX(id)
                FROM Analysis a
                WHERE a.execution_time < @bugCreationTime)