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,
  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


1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.