Avatar of Peter Nordberg
Peter NordbergFlag for Sweden

asked on 

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
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Peter Nordberg
Avatar of Norie
Norie

Peter

Why aren't you including EmployeesStoresByUser in the main query ?                          
Avatar of Limbeck
Limbeck

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

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

ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

@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)
>>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.
SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Somehow I don't think the author cares one way or the other or has moved on...
Avatar of Peter Nordberg

ASKER

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

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo