CODEWITHSUNDEEP
sqlsql-serverwhere-clause
james k
james k

Reputation: 29

I need help making a where clause with several pieces of criteria work

I have joined 6 tables to gather all of the information needed and am trying to use the where clause to filter the records I need with there corresponding information. However when I put the criteria in the where clause some parts work and some don't depending on what order they are in. One by one they all work no problem and even quite a few in pairs but when I put them all together some seem to take president over the others.

With this criteria I need only the null values from each table in the where clause along with anything in i.knum that = null and anything with a date older then 5/1/2019

select s.knum,m.knum as M_knum, d.knum as D_knum, o.knum as O_knum,
   e.knum as E_knum, s.customer_id, st.customer_id as ST_Customer_id, s.fdd,
   s.season, s.usage, s.unit_type, s.owner_type, s.status, s.state,
   (select max(HighTrw) from (values (trw1), (trw2), (trw3)) as Value(HighTrw)) as [high_trw],
   i.knum as Issued, i.date as Issued_Date
From dbo.SPI S
left join dbo.combined C on s.knum = c.k
Left join dbo.payments$ P on s.knum = p.knum 
Left join dbo.dvn D on s.knum = d.knum
Left join dbo.exclusion E on s.knum = e.knum
left join dbo.issued I on s.knum = i.knum
left join dbo.outfield O on s.knum = o.knum 
left join dbo.spi_tours ST on s.customer_id = st.customer_id
left join dbo.magna M on s.knum = m.knum
where (
       (i.date >= '2019-05-01')
       or (i.date is null)
      )
      and st.customer_id is null
      or st.Customer_id = ' '
      and m.knum is null
      and o.knum is null
      and e.knum is null
      and d.knum is null

knum    M_knum  D_knum  O_knum  E_knum  customer_id ST_Customer_id  fdd         season      usage   unit_type       owner_type      status  state   high_trw    Issued  Issued_Date
80571   NULL    NULL    80571   NULL    18640       NULL            2002-06-15  High Peak I Annual  Studio Deluxe   Royalty Wks Ann Full    NJ      647         NULL    NULL

Upvotes: 0

Views: 49

Answers (2)

pwilcox
pwilcox

Reputation: 5753

In addition to the concise and likely correct answer by avery_larry, I would also like to warn you that you probably want to brush up on operator precedence. In Sql Server, or gets processed before and. So your statement is equal to:

where     (
                  (i.date >= '2019-05-01' or i.date is null) 
              and st.customer_id is null
          ) 
or        (
                  st.Customer_id = ' ' 
              and m.knum is null 
              and o.knum is null 
              and e.knum is null 
              and d.knum is null
          )

Now, I'm almost certain that's not what you meant to write. At the very least, you wanted the customer_id logic together as a unit:

where     (
                  (i.date >= '2019-05-01' or i.date is null) 
              and (st.customer_id is null or st.Customer_id = ' ')
          ) 
or        (
                  m.knum is null 
              and o.knum is null 
              and e.knum is null 
              and d.knum is null
          )

But, as avery_larry pointed out, you likely didn't want conditions where ALL things are true, you wanted conditions where ANY thing is true.

where     i.date >= '2019-05-01' 
or        i.date is null
or        st.customer_id is null 
or        st.Customer_id = ' '
or        m.knum is null 
or        o.knum is null 
or        e.knum is null 
or        d.knum is null

Upvotes: 1

avery_larry
avery_larry

Reputation: 2135

Are you perhaps confusing AND with OR?

Consider this statement:
"I want the ones where m.knum is null and I also want the ones where o.knum is null."
The statement uses the word "and", but it describes an "or" condition.

Perhaps you want something like this:

where i.date >= '2019-05-01'
   or i.date is null
   or m.knum is null
   or o.knum is null
   or e.knum is null
   or d.knum is null
   or i.knum is null

I do not know how st.customer_id fits into this because you do not describe it.

Upvotes: 1

Related Questions