CODEWITHSUNDEEP
pythonsqlalchemy
Jisson
Jisson

Reputation: 3725

find missing value between to tables in sqlalchemy

I have two tables with a common field I want to find all the the items(user_id's) which present in the first table but not in the second.

Table1(user_id,...)
Table2(userid,...)

user_id in Table1 and Table2 are FKs pointing to same column in another table.

Upvotes: 1

Views: 433

Answers (2)

Jisson
Jisson

Reputation: 3725

session.query(Table1.user_id).outerjoin(Table2).filter(Table2.user_id == None)

Upvotes: 2

Trent
Trent

Reputation: 3103

This is untested as I'm still new to SQLAlchemy, but I think it should push you in the right direction:

table2 = session.query(Table2.user_id).subquery()
result = session.query(Table1).filter(Table1.user_id.notin_(table2))

my guess is this type of approach would result in the following SQL:

SELECT table1.* FROM table1 WHERE table1.user_id NOT IN (SELECT table2.user_id FROM table2)

Upvotes: 0

Related Questions