Narnia_Optimus Narnia_Optimus - 2 months ago 8
MySQL Question

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

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

enter image description here

2)

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