Reputation: 1725
I two tables (Loan_Contract
and Loan_Amend
) that have same column LoanID
. My purpose is that I want to get all data from table Loan_Contract
only if they are not exist in table Loan_Amend
.
So I tried my query as below:
db.session.query(
Loan_Contract.ID,
Loan_Contract.Currency,
Loan_Contract.DisbursedAmount
).\
join(Loan_Amend,Loan_Amend.LoanID != Loan_Contract.ID).\
all()
And
db.session.query(
Loan_Contract.ID,
Loan_Contract.Currency,
Loan_Contract.DisbursedAmount
).\
join(Loan_Amend,Loan_Amend.LoanID == Loan_Contract.ID).\
filter(Loan_Contract.ID != Loan_Amend.LoanID).\
all()
However, either of query above returned all record from Loan_Contract
even though LoanID
exist in Loan_Amend
.
What is correct way to archive result as expected above purpose? Thanks.
Upvotes: 0
Views: 1506
Reputation: 20548
To get all Loan_Contract
rows that don't have any Loan_Amend
referring to it, you need to use a LEFT JOIN
:
SELECT * FROM Loan_Contract LEFT JOIN Loan_Amend ON Loan_Contract.ID = Loan_Amend.LoanID
WHERE Loan_Amend.LoanID IS NULL;
Using SQLAlchemy:
session.query(Loan_Contract) \
.outerjoin(Loan_Amend, Loan_Contract.ID == Loan_Amend.LoanID) \
.filter(Loan_Amend.LoanID.is_(None))
Upvotes: 3