Solved

Smarter way to do a sql query with OR statement

Posted on 2011-09-03
10
180 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
[X]
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
10 Comments
 
LVL 34

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
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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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