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

Capture.PNG
tanj1035Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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
	AND ln.loanstoreID <> lt.transactionlocationstoreid
where lt.TransactionTypeID = 9 
	and lt.TransactionLocationStoreID in (select StoreID from tblStores_tblStoreTypes where storetypeID = 1)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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 ln.loanstoreID <> lt.transactionlocationstoreID

      AND lt.TransactionLocationStoreID IN (
            SELECT
                  StoreID
            FROM tblStores_tblStoreTypes
            WHERE storetypeID = 1
      )

Open in new window

but I would prefer to see you use a join instead of that IN (... )
PortletPaulEE Topic AdvisorCommented:
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.
tanj1035Author Commented:
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 CroweDatabase AdministratorCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.