Can you tell me how can I add ordering between LEFT and INNER JOIN. I think also that is where I am wrong, but not sure how to correct the ordering.
Generally...
FROM tbl1 LEFT JOIN (tbl2 JOIN tbl3 ON ...) tbl2_3 ON ...
In short, put parentheses around the join and give it an alias.
There are ordering rules that can be considered too but frankly I've never learned them and haven't noticed their absence. The above is the explicit way to do things and explicit is generally better.