asked on

SQL, How to do filter out

Hi Experts

My query below returned the result (see the screen shot). I want to only showing the result if loanstoreID<> transactionlocationstoreID.
For example, I want to show the result of loanID 1330114, not loanID 418282, 418323 etc...

How do I modify my query?

Thank you

select ln.loanID, ln.loanstoreID, lt.transactionlocationstoreid, ln.loandate, lt.transactiondatetime, lt.transactionamt
from tblLoans ln inner join 
tblLoanTransactions lt on ln. LoanID = lt. LoanID
where lt.TransactionTypeID=9 
and lt.TransactionLocationStoreID in (select StoreID from tblStores_tblStoreTypes where storetypeID=1)

Open in new window

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

e.g.

SELECT
      ln.loanID
    , ln.loanstoreID
    , lt.transactionlocationstoreid
    , ln.loandate
    , lt.transactiondatetime
    , lt.transactionamt
FROM tblLoans ln
      INNER JOIN tblLoanTransactions lt ON ln.LoanID = lt.LoanID
      INNER JOIN tblStores_tblStoreTypes s ON lt.TransactionLocationStoreID = s.StoreID
                  AND s.storetypeID = 1
WHERE lt.TransactionTypeID = 9
      AND ln.loanstoreID <> lt.TransactionlocationstoreID

Open in new window

but without tabkes/data to test against I'm guessing.

tanj1035

ASKER

Hi Paul,

What is the advantage of using inner join instead of sub-query? From my understanding, sub-query should better performance than join, maybe I am wrong.

Thank you,

Brian Crowe

A relational database like SQL Server is optimized to work with joined queries assuming your data is properly indexed. I didn't switch you subquery to an INNER JOIN because I am not familiar with your data. If the StoreTypes tables contains multiple records for each StoreID then you will get duplicate rows without implementing some grouping logic. If this is not an issue than a direct join as Paul suggests would be more efficient.

You can always pull up the profile and run the procedure with each and check the CPU & read stats to verify whether one is more efficient than the other. Comparing the actual execution plans can also be educational.