I always come across this question when searching for how to make LEFT JOIN depend on a further INNER JOIN. Here is an example for what I am searching when I am searching for “using LEFT JOIN and INNER JOIN in the same query”:
SELECT *
FROM foo f1
LEFT JOIN (bar b1
INNER JOIN baz b2 ON b2.id = b1.baz_id
) ON
b1.id = f1.bar_id
In this example, b1 will only be included if b2 is also found.