title: SQL - ROW_NUMBER that is used in a multi-condition LEFT JOIN
body: <p><a href="https://i.stack.imgur.com/FIJLp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FIJLp.png" alt="enter image description here" /></a>Two tables store different properties for each product: CTI_ROUTING_VIEW and ORD_MACH_OPS</p>
  <p>They are both organized by SPEC_NO &gt; MACH_SEQ_NO but the format of the Sequence number is different for each table so it can't be used for a JOIN. ORCH_MACH_OPS has MACHINE and PASS_NO, meaning if a product goes through the same machine twice, the row with the higher SEQ_NO will be PASS_NO 2, 3, etc. CTI_ROUTING_VIEW does not offer PASS_NO, but I can achieve the desired result with:</p>
  <pre><code>SELECT TOP (1000) [SPEC_NO]
        ,[SPEC_PART_NO]
        ,[MACH_NO]
        ,[MACH_SEQ_NO]
        ,[BLANK_WID]
        ,[BLANK_LEN]
        ,[NO_OUT_WID]
        ,[NO_OUT_LEN]
        ,[SU_MINUTES]
        ,[RUN_SPEED]
        ,[NO_COLORS]
        ,[PRINTDIEID]
        ,[CUTDIEID]
        ,ROW_NUMBER() OVER (PARTITION BY MACH_NO ORDER BY MACH_SEQ_NO) as PASS_NO
    FROM [CREATIVE].[dbo].[CTI_ROUTING_VIEW]
  </code></pre>
  <p>I would think that I could use this artificial PASS_NO as a JOIN condition, but I can't seem to get it to come through. This is my first time using ROW_NUMBER() so I'm just wondering if I'm doing something wrong in the JOIN syntax.</p>
  <pre><code>SELECT rOrd.[SPEC_NO]
        ,rOrd.[MACH_SEQ_NO]
        ,rOrd.[WAS_REROUTED]
        ,rOrd.[NO_OUT]
        ,rOrd.[PART_COMP_FLG]
        ,rOrd.[SCHED_START]
        ,rOrd.[SCHED_STOP]
        ,rOrd.[MACH_REROUTE_FLG]
        ,rOrd.[MACH_DESCR]
        ,rOrd.REPLACED_MACH_NO
        ,rOrd.MACH_NO
        ,rOrd.PASS_NO
        ,rWip.MAX_TRX_DATETIME
        ,ISNULL(rWip.NET_FG_SUM*rOrd.NO_OUT,0) as NET_FG_SUM
        ,CASE
          WHEN rCti.BLANK_WID IS NULL then 'N//A'
          ELSE CONCAT(rCti.BLANK_WID, ' X ', rCti.BLANK_LEN)
         END AS SIZE
       ,ISNULL(rCti.PRINTDIEID,'N//A') as PRINTDIEID
       ,ISNULL(rCti.CUTDIEID, 'N//A') as CUTDIEID
       ,rStyle.DESCR as STYLE
       ,ISNULL(rCti.NO_COLORS, 0) as NO_COLORS
       ,CAST(CONCAT(rOrd.ORDER_NO,'-',rOrd.ORDER_PART_NO) as varchar) as ORD_MACH_KEY
    FROM [CREATIVE].[dbo].[ORD_MACH_OPS] as rOrd
      LEFT JOIN (SELECT DISTINCT
         [SPEC_NO]
        ,[SPEC_PART_NO]
        ,[MACH_NO]
        ,MACH_SEQ_NO
        ,[BLANK_WID]
        ,[BLANK_LEN]
        ,[NO_COLORS]
        ,[PRINTDIEID]
        ,[CUTDIEID]
        ,ROW_NUMBER() OVER (PARTITION BY MACH_NO ORDER BY MACH_SEQ_NO) as PASS_NO
      FROM [CREATIVE].[dbo].[CTI_ROUTING_VIEW]) as rCti
      ON rCti.SPEC_NO = rOrd.SPEC_NO
      and rCti.MACH_NO =
          CASE 
              WHEN rOrd.REPLACED_MACH_NO is null then rOrd.MACH_NO
              ELSE rOrd.REPLACED_MACH_NO
          END
      and rCti.PASS_NO  = rOrd.PASS_NO
      LEFT JOIN INVENTORY_ITEM_TAB as rTab
      ON rTab.SPEC_NO = rOrd.SPEC_NO
      LEFT JOIN STYLE_DESCRIPTION as rStyle
      ON rStyle.DESCR_CD = rTab.STYLE_CD
        LEFT JOIN (
        SELECT
          JOB_NUMBER
          ,FORM_NO
          ,TRX_ORIG_MACH_NO
          ,PASS_NO
          ,SUM(GROSS_FG_QTY-WASTE_QTY) as NET_FG_SUM
          ,MAX(TRX_DATETIME) as MAX_TRX_DATETIME
        FROM WIP_MACH_OPS
        WHERE GROSS_FG_QTY &lt;&gt; 0
        GROUP BY JOB_NUMBER, FORM_NO, TRX_ORIG_MACH_NO, PASS_NO) as rWip
      ON rWip.JOB_NUMBER = rOrd.ORDER_NO
      and rWip.FORM_NO = rOrd.ORDER_PART_NO
      and rWip.TRX_ORIG_MACH_NO = rOrd.MACH_NO
      and rWip.PASS_NO = rOrd.PASS_NO
    WHERE rOrd.SCHED_START &gt; DATEADD(DAY, -20, GETDATE())
  </code></pre>