SQL Server Right Join – Right Join with Condition Returns Non-Matching Rows

sql-server-2008-r2

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'  -- <<< 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;

enter image description here

really appreciate any help on this

Best Answer

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.

Related Question