Right Join within a Left Join in SQL Server Query

Question

I'm in the process of converting some MS Access queries into Transact-SQL format and have run into some problems. Is there a way to write a Join within a Join?
For example:

LEFT JOIN (TaxInfo RIGHT JOIN TaxInfoJackpot 
           ON TaxInfo.RefNumber = TaxInfoJackpot.RefNumber) 
ON HandPay.SlipNumber = TaxInfoJackpot.SlipNumber

This is just a snapshot of a much larger query of course. But, if anyone knows if this is possible any help would be great.

Thanks in advance.

Answer 1

You can do this with a subquery.

LEFT JOIN (
    SELECT * 
    FROM TaxInfo ti
    RIGHT JOIN TaxInfoJackpot j ON ti.RefNumber = j.RefNumber
) tij ON HandPay.SlipNumber = tij.SlipNumber

But I'm not sure if you actually need to do it this way. I think you can do this with just normal joins

FROM HandPay h
RIGHT JOIN TaxInfoJackpot j ON h.SlipNumber = j.SlipNumber
LEFT JOIN TaxInfo ti ON j.RefNumber = ti.RefNumber;

Answer 2

I tend to like all of my joins to be sequential and flowing in the same direction, when possible (and I try to always re-order things so it is possible). LEFT JOIN / RIGHT JOIN / ON / ON is very confusing to follow for anyone, myself included, and I've been doing this for a very long time. Access certainly doesn't do anyone any favors with the bizarre syntax it pumps out (and accepts).

I am not sure if the current syntax provides the results you expect, but can you compare to this format to see if they're the same? Hard to know for sure without sample data and desired results.

SELECT ...
  FROM dbo.TaxInfoJackPot AS jp
  LEFT OUTER JOIN dbo.HandPay AS hp
    ON hp.SlipNumber = jp.SlipNumber
  LEFT OUTER JOIN dbo.TaxInfo AS ti
    ON jp.RefNumber = ti.RefNumber;