Including another join in MYSQL table

Question

I have created the following query which includes 2 joins:

SELECT a.name, b.tid
FROM table1 a INNER JOIN
     table2 b
     ON a.id = b.id
INNER JOIN (
  SELECT b2.id, b2.status,  MAX(b2.created_time) as max_time
  FROM oac.qualys_scan b2
  GROUP BY b2.id  ,  b2.qualys_type
  ) t on t.id = a.id  
      AND  t.status=b.status
      AND t.max_time = b.created_time
WHERE b.status = 'FAIL';

The output is coming as follows:

id      tid     name
17695   19512   abc.com
17781   19628   abc1.com
17805   19732   abc2.com
17806   19703   abc3.com
17807   19704   abc4.com

I have another table table3 which has following values

id  tid     name                        details
842 19512   abc.com                     Details Description 1
843 19628   abc1.com                    Details Description 2

I want to join the above query with table3 on tid such that I get the following output

id      tid     name          details      
17695   19512   abc.com       Details Description 1
17781   19628   abc1.com      Details Description 1

Answer 1

Then it's just an other inner join on table1 a :

 SELECT a.id, b.tid, a.name,  table3.details
    FROM table1 a INNER JOIN
         table2 b
         ON a.id = b.id
    INNER JOIN (
      SELECT b2.id, b2.status,  MAX(b2.created_time) as max_time
      FROM oac.qualys_scan b2
      GROUP BY b2.id  ,  b2.qualys_type
      ) t on t.id = a.id  
          AND  t.status=b.status
          AND t.max_time = b.created_time
    INNER JOIN table3 on b.tid = table3.tid
    WHERE b.status = 'FAIL';