Adding fourth table in mysql join

Question

I have written a query which involves 3 joins and 3 tables as follows:

SELECT 
T3.fault, count(*)
FROM table2 T2 
INNER JOIN (
    SELECT a.*
    FROM table1 a
    LEFT JOIN table1 b ON a.item_id = b.item_id AND a.submit_id < b.submit_id
    WHERE b.submit_id IS NULL
) T1  ON T1.item_id = T2.item_id
INNER JOIN table3 T3 ON T1.id = T3.run_id
group by T3.fault
order by count(*) desc;

My table3 looks as follows:

id      run_id  runname_id  status  fault
134049  16736   312         FAIL    error1
134050  16736   313         FAIL    error2
134051  16736   314         FAIL    error3
134052  16736   315         PASS    error4
134053  16736   316         PASS    error5

I have a static table table4 which looks as follows:

id  name
312 name1
313 name2
314 name3
315 name4
316 name5

I want to include table4 in the query so that my output should also include name from table4. Every error has a corresponding name in table4 I want to include that name as well

fault   count(*) name
error1  6        name1

Answer 1

So just LEFT JOIN that table4, and T4.name column to selected columns list.

SELECT 
T3.fault, count(*), t4.name
FROM table2 T2 
INNER JOIN (
    SELECT a.*
    FROM table1 a
    LEFT JOIN table1 b ON a.item_id = b.item_id AND a.submit_id < b.submit_id
    WHERE b.submit_id IS NULL
) T1  ON T1.item_id = T2.item_id
INNER JOIN table3 T3 ON T1.id = T3.run_id
LEFT JOIN table4 T4
ON 3.runname_id  = T4.id
group by T3.fault
order by count(*) desc;