sql server right join with condition returns rows that do not match condition
To elaborate a bit on sp_BlitzErik's comments above:

Your RIGHT JOIN says the following:


  I want all rows from d, with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15) and d.CARRIERGUID='production'.


If d.CARRIERGUID = 'production', then you still take the row from d - you just exclude any rows from the a-c INNER JOIN that might otherwise match.

If what you really meant was:


  I want all rows from d where d.CARRIERGUID='production', with the data from any rows from the a-c join where a.id = substring(d.FILENAME,1,15).


then you would need to take d.CARRIERGUID='production' out of the RIGHT JOIN ON clause, and make it the WHERE clause for the query as a whole.