• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31
  • Last Modified:

SQL Query

-- The code below works as it is but I would like to add another condition.
          
                  --   when            m1.Question in ('Silky', 'Hard')  and    m1.Value = 'Y'  are true then I want  to check for       
               
              --                        and    m1.Question in ('Misc')  and  m1.Value = 'N'

              --                        how do I add this condition?

Here is the code:
                  
            Select    m1.*, m2.*    

            from Livedb.dbo.Main   m1 with (NOLOCK)

            left join Livedb.dbo.Main m2  with (nolock)
            on m1.OrdID = m2.OrdID
            

            where isnull(m1.DateTime)   between @BeginDate and @EndDate  
            and livedb.dbo.fxAge(m1.Orderdate,m1.DateTime)  > 7
            and m1.ItemNo = '100740'  

            and    m1.Question in ('Silky', 'Hard')
            and    m1.Value = 'Y'
                        

      new condition  --> and  m1.Question in   ('Misc')        and        m1.Value =  'N'
0
Abdul Khan
Asked:
Abdul Khan
  • 3
  • 2
  • 2
1 Solution
 
_agx_Commented:
Add an OR condition, but be sure to wrap each SETof conditions in parenthesis so it evaluates correctly

        WHERE .....
        AND ....
        AND      and m1.ItemNo = '100740'  
        AND  (    
                        (
 m1.Question in ('Silky', 'Hard') and    m1.Value = 'Y' )
                        OR
                         ( m1.Question in   ('Misc')  and        m1.Value =  'N' )
                 )
0
 
awking00Commented:
...
where isnull(m1.DateTime)   between @BeginDate and @EndDate  
             and livedb.dbo.fxAge(m1.Orderdate,m1.DateTime)  > 7
             and m1.ItemNo = '100740'  
             and ((m1.Question in ('Silky', 'Hard') and m1.Value = 'Y')
               or (m1.Question in ('Misc') and m1.Value =  'N'))
0
 
awking00Commented:
Sorry, _agx_, Didn't see your comment before I posted :-)
1
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
_agx_Commented:
awking00 - Great minds think alike :-)
0
 
_agx_Commented:
@Abdul Khan

Nothing to do with your question, but if "DateTime" column is indexed, it's better not to use functions on it, since they'll prevent the db from using the index. Instead, use a comparison like this:

WHERE  m1.DateTime >=  @BeginDate
AND     m1.DateTime <= @EndDate

The exact syntax depends on what type of comparison you're performing and whether or not the values include a "Time" portion
0
 
Abdul KhanAuthor Commented:
@_agx_  and @awking00 Thank you so much for your quick assistance. And I appreciate the insight regarding the functions.
0
 
Abdul KhanAuthor Commented:
@_agx_  and @awking00  thanks both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now