CODEWITHSUNDEEP
python-2.7sqlalchemy
Houy Narun
Houy Narun

Reputation: 1725

SqlAlchemy: Join Two Tables With Not Equal Condition

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

Answers (1)

univerio
univerio

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

Related Questions