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

Smarter way to do a sql query with OR statement

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
peternordberg
Asked:
peternordberg
2 Solutions
 
NorieCommented:
Peter

Why aren't you including EmployeesStoresByUser in the main query ?                          
0
 
LimbeckCommented:
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
 
Anthony PerkinsCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
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
 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
@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
 
Anthony PerkinsCommented:
>>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
 
awking00Commented:
See attached.
query.txt
0
 
Anthony PerkinsCommented:
Somehow I don't think the author cares one way or the other or has moved on...
0
 
peternordbergAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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