MySQL writing join on three tables

Question

I have written the following mysql query

SELECT distinct name, date
FROM table1
JOIN table2 ON (table2.item_id = table1.item_id)
where table1.id IN (SELECT run_id FROM 
table3 where table3.status = 'FAIL') and table1.createdate >= '2011-01-01'
 ; 

I want to include another field message from table3 in my output.

I guess this might involve joining 3 tables in the query. Please let me know how to achieve this.

Answer 1

There's not quite enough info to fully answer the question. We also want to know:

1. Does the status column come from table1 or from table3
2. Can there be more than one table3 record with a runid that matches a table1.id value?
3. If there can be, which table3.message value do you want to show?

Without knowing that info, all we can do is guess. Here is my guess:

SELECT distinct name, date, message
FROM table1 t1
INNER JOIN table2 t2 ON t2.item_id = t1.item_id
INNER JOIN table3 t3 on t1.id =t3.run_id AND t3.status = 'FAIL'
WHERE t1.createdate >= '2011-01-01'