sql server right join with condition returns rows that do not match condition I'm having problem with my right join. Below is my query;

select a.maintitle as TITLE,
       a.id AS ID,
       a.a_auto_produ AS HOUSENO,
       c.CARRIERGUID,
       substring(d.FILENAME,1,15),
       d.CARRIERGUID
  from a
         inner join c on a.id = substring(c.FILENAME,1,15)
                         and  c.CARRIERGUID='xxx'
         right join d on a.id = substring(d.FILENAME,1,15)
                         and d.CARRIERGUID='production'  -- &lt;&lt;&lt; here
;


with the condition indicated. I expect to have carrierguid = Production returned. However the returned rows also contain carrierguid other than production as you may see below;



really appreciate any help on this
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.