left join with where clause

MySQL LEFT Inner and outer where clause joins