Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Smarter way to do a sql query with OR statement

Posted on 2011-09-03
10
174 Views
Last Modified: 2012-06-21
HI,

I have a sql query looking like this:
SELECT        AttestInvoice.verNo, AttestInvoice.invoiceCompany, AttestInvoice.storeID, AttestInvoice.invoiceSum, AttestInvoice.invoiceID, AttestAccountInfo.completedDate
FROM            AttestInvoice INNER JOIN
                         AttestAccountInfo ON AttestInvoice.invoiceID = AttestAccountInfo.invoiceID
WHERE        (AttestInvoice.storeID > 0) AND (AttestInvoice.storeID IN
                             (SELECT        storeID
                               FROM            EmployeesStoresByUser
                               WHERE        (userName = @userName))) AND (AttestAccountInfo.approved = 1)
                               OR (AttestInvoice.storeID > 0) AND (AttestInvoice.storeID IN
                             (SELECT        storeID
                               FROM            EmployeesStoresByUser
                               WHERE        (userName = @userName))) AND (AttestAccountInfo.denied = 1)
ORDER BY AttestAccountInfo.completedDate DESC

Open in new window


As you can see it contains an OR statement. The only thing I want to do is to get all invoices that are approved or denied, but when I do that I need to re-write all the other statements too, otherwise it leaves or adds some data in the result.

Is there a smarter way to make OR queries without having to re-write all things over and over after each OR?

Thanks for help!

Peter
0
Comment
Question by:peternordberg
10 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 36477866
Peter

Why aren't you including EmployeesStoresByUser in the main query ?                          
0
 
LVL 19

Expert Comment

by:Limbeck
ID: 36477883
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

      
something like
SELECT        AttestInvoice.verNo, AttestInvoice.invoiceCompany, AttestInvoice.storeID, AttestInvoice.invoiceSum, AttestInvoice.invoiceID, AttestAccountInfo.completedDate
FROM            AttestInvoice INNER JOIN
                         AttestAccountInfo ON AttestInvoice.invoiceID = AttestAccountInfo.invoiceID
WHERE        (AttestInvoice.storeID > 0) AND (AttestInvoice.storeID IN
                             (SELECT        storeID
                               FROM            EmployeesStoresByUser
                               WHERE        userName = @userName AND ((AttestAccountInfo.approved = 1) or (AttestAccountInfo.denied = 1)) and EmployeesStoresByUser.storeID=AttestInvoice.storeID)
 
ORDER BY AttestAccountInfo.completedDate DESC
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36478183
Try it this way:
SELECT  i.verNo,
        i.invoiceCompany,
        i.storeID,
        i.invoiceSum,
        i.invoiceID,
        a.completedDate
FROM    AttestInvoice i
        INNER JOIN AttestAccountInfo a ON i.invoiceID = a.invoiceID
        INNER JOIN EmployeesStoresByUser e ON i.StoreID = e.StoreID
WHERE   a.storeID > 0
        AND e.userName = @userName
        AND (a.approved = 1 OR a.denied = 1)
ORDER BY aai.completedDate DESC

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36478192
Lets try that again (small typo):
SELECT  i.verNo,
        i.invoiceCompany,
        i.storeID,
        i.invoiceSum,
        i.invoiceID,
        a.completedDate
FROM    AttestInvoice i
        INNER JOIN AttestAccountInfo a ON i.invoiceID = a.invoiceID
        INNER JOIN EmployeesStoresByUser e ON i.StoreID = e.StoreID
WHERE   a.storeID > 0
        AND e.userName = @userName
        AND (a.approved = 1 OR a.denied = 1)
ORDER BY 
        a.completedDate DESC

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 300 total points
ID: 36478574
well practice using brackets sparingly and correctly

you should just use them where needed , and intelligently around the logic that is
concerned with the OR parts of the query...

write the common query requirements first...
and then follow it with and conditions with bracketed OR's

aliasing the table names would also make the query more readable...

you could also write it as
 and 1 in (attestaccountinfo.approved,attestaccountinfo.declined)
but i wouldn't recommend that from a query sargability point of view...

SELECT        AttestInvoice.verNo, AttestInvoice.invoiceCompany, AttestInvoice.storeID
, AttestInvoice.invoiceSum, AttestInvoice.invoiceID, AttestAccountInfo.completedDate
FROM            AttestInvoice 
INNER JOIN      AttestAccountInfo 
  ON AttestInvoice.invoiceID = AttestAccountInfo.invoiceID
WHERE AttestInvoice.storeID > 0
  AND AttestInvoice.storeID IN
                             (SELECT   storeID
                               FROM   EmployeesStoresByUser
                               WHERE  userName = @userName
                             )
       
  AND (   AttestAccountInfo.approved = 1
       OR AttestAccountInfo.denied = 1
      )
ORDER BY AttestAccountInfo.completedDate DESC

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36478624
@acperkins

i not sure how you can translate the subquery into a join?
surely you've now introduced extra rows (of its a 1:m relationship)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36479337
>>i not sure how you can translate the subquery into a join?<<
To be precise it is a correlated subquery.  But I believe that my "translation" is perfectly correct and appropriate.  But I guess we will have to let the author decide.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 36496089
See attached.
query.txt
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36499821
Somehow I don't think the author cares one way or the other or has moved on...
0
 

Author Closing Comment

by:peternordberg
ID: 36716470
Both these query's work fine. Thanks extra to LowFatSpread for the explanation since I was concerned with having to repeat a lot of code to achieve the desired scenario.

Also, sorry for delay in answering since I had to change project all of a sudden and had no time to review this until now.

Peter
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question