CODEWITHSUNDEEP
sqlsql-servert-sql
Juan Pedro
Juan Pedro

Reputation: 175

Inner join have multiple ON clauses into last join

I'm doing maintenance to OLD sql database and I have a select clause like this:

SELECT
              [P].[ItemID]
            , SUM([p].[OnOrder]) AS [OnOrder]
            , [I].[FullName] AS [PartNo]
            , [I].[PurchaseDesc] AS [Description]
            , [I].[ItemCust3]
              FROM @POItems AS [P]
                  INNER JOIN [tblMakeUseType] AS [MUT] WITH(NOLOCK)
                  INNER JOIN [ItemInventory] AS [I] WITH(NOLOCK) ON [MUT].[MakeUseTypeID] = [I].[MakeUseTypeID] ON [P].[ItemID] = [I].[NCItemInventoryID]

As you can see first INNER JOIN does not have ON clause, but after second inner join there is ON clause of first INNER JOIN, I never seen that before, is there any way to do reverse engeneering and use as tipcal INNER JOIN? I mean each one with hes ON clause? Regards

Upvotes: 4

Views: 729

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Your query is interpreted as:

FROM @POItems [P] INNER JOIN
     ([tblMakeUseType] [MUT] INNER JOIN
      [ItemInventory] [I] 
      ON [MUT].[MakeUseTypeID] = [I].[MakeUseTypeID]
     )
    ON [P].[ItemID] = [I].[NCItemInventoryID]

This is fine. But you can write it more conventionally as:

FROM [tblMakeUseType] [MUT] INNER JOIN
     [ItemInventory] [I] 
     ON [MUT].[MakeUseTypeID] = [I].[MakeUseTypeID] INNER JOIN
     @POItems [P]
     ON [P].[ItemID] = [I].[NCItemInventoryID]

Or:

FROM @POItems [P] INNER JOIN
     [ItemInventory] [I] 
     ON [P].[ItemID] = [I].[NCItemInventoryID] INNER JOIN
     [tblMakeUseType] [MUT]
     ON [MUT].[MakeUseTypeID] = [I].[MakeUseTypeID]

The order of the tables does not matter for inner joins, although the tables have to be declared before their aliases can be used for ON conditions.

Upvotes: 3

Related Questions