Adjit Adjit - 1 month ago 10
SQL Question

SQL join tables and filter based on comparisons

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
ITEMNMBR
s 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

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
Comments