SqlAlchemy: filter to match all instead of any values in list?

mysql, python, sql, sqlalchemy

Solution

You are looking for a query that works on sets of rows. I think a group by with having clause is the best approach:

select aid
from jt
where bid in (<your list>)
group by aid
having count(distinct bid) = 2

If you can put the ids that you desire in a table, you can do the following more generic approach:

select aid
from jt join
     bids
     on jf.bid = bids.bid
group by aid
having count(distinct jt.bid) = (select count(*) from bids)

Problem

I want to query a junction table for the value of column `aID` that matches all values of a list of ids `ids=[3,5]` in column `bID`. This is my junction table (`JT`): ``` aID bID 1 1 1 2 2 5 2 3 1 3 3 5 ``` I have this query: `session.query(JT.aID).filter(JT.bID.in_(ids)).all()` This query returns the `aID` values `1`, `2` and `3` because they all have rows with either `3` or `5` in the `bID` column. What I want the query to return is `2` because that is the only `aID` value that has all values of the `ids` list in its `bID` column. Don't know how to explain the problem better, but how can I get to the result?

Original source