Solved

Smarter way to do a sql query with OR statement

Posted on 2011-09-03
10
172 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

932 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now