Smarter way to do a sql query with OR statement
HI,
I have a sql query looking like this:
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
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
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
Last Comment
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
something like
SELECT AttestInvoice.verNo, AttestInvoice.invoiceCompa
FROM AttestInvoice INNER JOIN
AttestAccountInfo ON AttestInvoice.invoiceID = AttestAccountInfo.invoiceI
WHERE (AttestInvoice.storeID > 0) AND (AttestInvoice.storeID IN
(SELECT storeID
FROM EmployeesStoresByUser
WHERE userName = @userName AND ((AttestAccountInfo.approv
ORDER BY AttestAccountInfo.complete
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
something like
SELECT AttestInvoice.verNo, AttestInvoice.invoiceCompa
FROM AttestInvoice INNER JOIN
AttestAccountInfo ON AttestInvoice.invoiceID = AttestAccountInfo.invoiceI
WHERE (AttestInvoice.storeID > 0) AND (AttestInvoice.storeID IN
(SELECT storeID
FROM EmployeesStoresByUser
WHERE userName = @userName AND ((AttestAccountInfo.approv
ORDER BY AttestAccountInfo.complete
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
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
ASKER CERTIFIED SOLUTION
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.
@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?
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.
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
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...
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
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 (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
TRUSTED BY
Why aren't you including EmployeesStoresByUser in the main query ?