Smarter way to do a sql query with OR statement

Peter

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

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

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

Select all Open in new window

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

Somehow I don't think the author cares one way or the other or has moved on...

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