SQL, How to do filter out

tanj1035
tanj1035 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Administrator
Top Expert 2005
Commented:
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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

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 Administrator
Top Expert 2005

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial