Select with multi column where issue

Transact-SQL
1

I have the following select statement. What I am basically trying to do is select all rows where the manager and year are equal to what is shown in the sql below and the columns fcst_jan through fcst_dec does not contain one of those values. It seems to work find for some, but I am still getting rows that contain 205 in one of the columns. So basically i want to select rows where any column jan through dec doesnt contain one of those values. Please help. I can't figure it out. I know my table design isnt the best way of doing it, but it is what I have, so i need to figure it out.

SELECT        fcst_id, fcst_emplname, fcst_emplid, fcst_posid, fcst_mgrid, fcst_sect_id, fcst_year, fcst_jan, fcst_feb, fcst_mar, fcst_apr, fcst_may, fcst_jun, fcst_jul, fcst_aug, 
                         fcst_sep, fcst_oct, fcst_nov, fcst_dec, fcst_comments
FROM            EMS.dbo.TEMSFCST
WHERE        
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_jan NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_feb NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_mar NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_apr NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_may NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_jun NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_jul NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_aug NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_sep NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_oct NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_nov NOT IN (1, 2, 3, 4, 5, 205, 211)) OR
(fcst_mgrid = '00809571') AND (fcst_year = 2016) AND (fcst_dec NOT IN (1, 2, 3, 4, 5, 205, 211))
2

If, say, fcst_jan has a value NOT IN (1, 2, 3, 4, 5, 205, 211) it will be selected (if it also has "fcst_mgrid = '00809571' AND fcst_year = 2016"). HOWEVER, that row MIGHT have fcst_feb = 205 - which is perhaps what you are seeing?

If you only want rows where NONE of fcst_jan, fcst_feb, ..., fcst_dec match those values then you need AND instead of OR (and you don't need to repeat the tests for fcst_mgrid / fcst_year on each row.

The other problem you might be having is that you have

A=1 AND B=2 AND C NOT IN (3, 4) OR
A=1 AND B=2 AND C NOT IN (3, 4) OR
...

and the precedence rules for AND / OR will apply (and are PROBABLY NOT WHAT YOU WANT!)

Take this example:

I would like a HAMBURGER AND FRIES OR HASH-BROWNS
```
and your mate comes back with just some hash Browns and no burger!
If you had said
````text
I would like a HAMBURGER AND ( FRIES OR HASH-BROWNS )
```
he would have done the right thing!

So you probably need some parentheses in your query (although I think it is more likely that you need AND instead of OR)
3

So I should have put my original. I did what you said. In the statement below it I use 'and' then no records are returned. I know there should be 8 records. With 'or' i get 10 records, with 'and' i get 0 records.

SELECT [fcst_id]
      ,[fcst_emplname]
      ,[fcst_emplid]
      ,[fcst_posid]
      ,[fcst_mgrid]
      ,[fcst_sect_id]
      ,[fcst_year]
      ,[fcst_jan]
      ,[fcst_feb]
      ,[fcst_mar]
      ,[fcst_apr]
      ,[fcst_may]
      ,[fcst_jun]
      ,[fcst_jul]
      ,[fcst_aug]
      ,[fcst_sep]
      ,[fcst_oct]
      ,[fcst_nov]
      ,[fcst_dec]
      ,[fcst_comments]
  FROM [EMS].[dbo].[TEMSFCST]
  where fcst_mgrid='00809571' and fcst_year=2016 and 
  (
  fcst_jan not in (1,2,3,4,5,205,211) or 
  fcst_feb not in (1,2,3,4,5,205,211) or 
  fcst_mar not in (1,2,3,4,5,205,211) or 
  fcst_apr not in (1,2,3,4,5,205,211) or 
  fcst_may not in (1,2,3,4,5,205,211) or 
  fcst_jun not in (1,2,3,4,5,205,211) or
  fcst_jul not in (1,2,3,4,5,205,211) or 
  fcst_aug not in (1,2,3,4,5,205,211) or 
  fcst_sep not in (1,2,3,4,5,205,211) or
  fcst_oct not in (1,2,3,4,5,205,211) or 
  fcst_nov not in (1,2,3,4,5,205,211) or 
  fcst_dec not in (1,2,3,4,5,205,211)
  )
4

Is the value for fcst_jan ... fcst_dec NULL for the row(s) that you want to select?

fcst_jan not in (1,2,3,4,5,205,211)

will NOT match a row where fcst_jan IS NULL (unless one of the other OR conditions matches a column which is NOT NULL and has a value that is NOT IN (1,2,3,4,5,205,211)

Well ... "Yes and No" :slight_smile:

You new code will match a row that has a value for fcst_jan that is NOT IN (1,2,3,4,5,205,211) which ALSO has a value for fcst_feb ... fcst_dec which IS in that list. I don't know if you wanted to exclude that?

But, that not withstanding, your query should have matched the 8 records you were expecting to be matched.

What are the values for fcst_jan ... fcst_dec for one of the rows that you would expect to be matched?

5

yes fcst_jan or any other can be a NULL value.

6

fcst_jan fcst_feb fcst_mar fcst_apr fcst_may fcst_jun fcst_jul fcst_aug fcst_sep fcst_oct fcst_nov fcst_dec
NULL NULL NULL NULL NULL NULL NULL NULL 50 50 50 205

7

That's not what I meant, unless ALL the rows you are trying to select have ALL columns fcst_jan ... fcst_dec ALL set to NULL ?

Your row should be selected if:

Any column fcst_jan ... fcst_dec has a value which is NOT NULL and is NOT IN (1,2,3,4,5,205,211)

provided that that row also has fcst_mgrid='00809571' and fcst_year=2016

8

Sorry, my reply crossed with yours.

That row matches fcst_sep NOT IN (1,2,3,4,5,205,211) (and also same for fcst_oct & fcst_nov) provided that that row also has fcst_mgrid='00809571' and fcst_year=2016

9

that row should not show up because fcst_dec has 205, which is one of the values I am checking for NOT IN (1,2,3,4,5,205,211)

10

Sounds like what you need is:

where fcst_mgrid='00809571' and fcst_year=2016 and 
(fcst_jan not in (1,2,3,4,5,205,211) OR fcst_jan IS NULL) AND
(fcst_feb not in (1,2,3,4,5,205,211) OR fcst_feb IS NULL) AND
...
1 Like