INNER JOIN within a LEFT JOIN clause

Question

I was given a query that uses some very weird syntax for a join and I need to understand how this join is being used:

SELECT
  T1.Acct#
, T2.New_Acct#
, T3.Pool#
FROM        DB.dbo.ACCT_TABLE       T1
LEFT JOIN   DB.dbo.CROSSREF_TABLE   T2
INNER JOIN  DB.dbo.POOL_TABLE       T3
        ON  T2.Pool# = T3.Pool#
        ON  T1.Acct# = T2.Prev_Acct#

• T1 is a distinct account list
• T2 is a distinct account list for each Pool #
• T3 is a distinct pool list (group of accounts)

I need to return the previous account number held in T2 for each record in T1. I also need the T3 Pool# returned for each pool.

What I'm trying to understand is why someone would write the code this way. It doesn't make sense to me.

Answer 1

A little indenting will show you better what was intended

SELECT
  T1.Acct#
, T2.New_Acct#
, T3.Pool#
FROM        DB.dbo.ACCT_TABLE       T1
LEFT JOIN   DB.dbo.CROSSREF_TABLE   T2
     INNER JOIN  DB.dbo.POOL_TABLE  T3
     ON  T2.Pool# = T3.Pool#
ON  T1.Acct# = T2.Prev_Acct#

This is a valid syntax that forces the join order a bit. Basically it is asksing for only the records in table T2 that are also in table T3 and then left joining them to T1. I don't like it personally as it is confusing for maintenance. I would prefer a derived table as I find those much clearer and much easier to change when I need to do maintenance six months later:

SELECT
  T1.Acct#
, T2.New_Acct#
, T3.Pool#
FROM        DB.dbo.ACCT_TABLE       T1
LEFT JOIN   (select T2.New_Acct#, T3.Pool#
             FROM DB.dbo.CROSSREF_TABLE   T2
             INNER JOIN  DB.dbo.POOL_TABLE       T3
                ON  T2.Pool# = T3.Pool#) T4
   ON  T1.Acct# = T4.Prev_Acct#