SQL join tables and filter based on comparisons

Question

I have a query where if I change my on condition to a where later in the query I get different results, and would just like an explanation as to why the result is different and how to get one versus the other.

SELECT * FROM
    (SELECT
            tHeader.SOPNUMBE AS [Invoice Number],
            tHeader.ACTLSHIP AS [Invoice Date],
            tHeader.CUSTNMBR,
            tHeader.CUSTNAME,
            tHeader.SLPRSNID

            FROM METRO.dbo.SOP30200 tHeader
            WHERE
                MONTH(ACTLSHIP) = 6
                AND YEAR(ACTLSHIP) = 2016
                AND SOPNUMBE like 'I%') header
    JOIN
        (SELECT *
            FROM METRO.dbo.SOP30300
            WHERE CSLSINDX = 137
        ) lineItems
        on
            lineItems.SOPNUMBE = header.[Invoice Number]
            /* ISSUE COMPARISON 1 --- Returns 1265 rows ---
            AND lineItems.ITEMNMBR NOT LIKE '%ENVISION%'
            AND lineItems.ITEMNMBR NOT LIKE '%REBATE%'
            AND lineItems.ITEMNMBR NOT LIKE '%EPSON%'*/   
    LEFT JOIN 
        (SELECT 
            cogs.ITEMNMBR,
            cogs.ITMSHNAM
            FROM METRO.dbo.IV00101 cogs
            WHERE
                cogs.IVCOGSIX = 137
        ) gpItems
        on gpItems.ITEMNMBR = lineItems.ITEMNMBR
        /* ISSUE COMPARISON 2 --- Returns 1255 rows ---*/
        WHERE
            gpItems.ITMSHNAM <> 'TM'
            AND gpItems.ITMSHNAM <> 'Rebate'
ORDER BY SOPNUMBE

So a quick explanation of this - any rows that I get from lineItems table they have an ITEMNMBR column. Associated to that column from the gpItems table is ITMSHNAM, and not every ITEMNMBR from the lineItems table is in the gpItems table. The ITEMNMBR column is a little too ambiguous, and subject to change. So I would like to filter my results based on ITMSHNAM not being equal to TM or Rebate, yet I still want to return any rows that don't have an ITMSHNAM.

The comparisons are equivalent, because any of the lineItems.ITEMNMBR comparisons will always have those gpItems.ITMSHNAM comparisons, but like I said, those ITEMNMBRs are subject to change.

If I use ISSUE COMPARISON 1 and comment out the second comparison it gets me the results I want with a row count of 1265, however, because of the reasoning above, I want to use the gpItems comparison to filter the results. But when I use ISSUE COMPARISON 2 my row count goes down to 1255. I looked to see what items are excluded, and it seems it is excluding any values that don't have an TIMSHNAM.

How can I fix my gpItems comparison to return the required 1265 rows?

Answer 1

Try adding one more condition to your constraints to allow returning rows where gpItems.ITMSHNAM is null.

Change:

WHERE
  gpItems.ITMSHNAM <> 'TM'
  AND gpItems.ITMSHNAM <> 'Rebate'

To this:

WHERE
  gpItems.ITMSHNAM NOT IN ('TM', 'Rebate')
  OR gpItems.ITMSHNAM IS NULL