Question: I have a Association table of movies and categories that belong to those movies, and I want to get all the common categories between two movies (just need the id of the category).

So if both the movies have the category 'Thriller' which has the category\_id of 5, i want to get 5. And if they have no common categories it just returns None.

Table looks like:

```
class MovieCategoryScores(db.Model):
    movie_id = db.Column(db.Integer, db.ForeignKey('movie.id'), primary_key=True)
    category_id = db.Column(db.Integer, db.ForeignKey('category.id'), primary_key=True)
    score = db.Column(db.Integer)
    votes = db.Column(db.Integer)
    category = relationship("Category", back_populates="movies")
    movie = relationship("Movie", back_populates="categories")

```

I know I can query
`categories = MovieCategoryScores.query.filter(MovieCategoryScores.movie_id.in_([movie1, movie2])).all()`
to get ALL categories, and I tried putting `(MovieCategoryScores.category_id)` after the query to only get the id's but that didn't work and I just got a `TypeError: 'BaseQuery' object is not callable` error.

If I figured out how to *just* get the ID's I could use something like:

```
categories.sort()
for index, category_id in enumerate(categories.copy()):
    if categories[index+1] != category_id:
        categories[index].remove()
return categories

```

To get get a list of only the id's that that there are 2 of, but it feels like there should be some better way to be able to the ID's of the items where both have the same category\_id just through a query command?

Either solution will be much appreciated!

Answer: 
You can use `having` and `func.count() > 1` to get the opposite of `distinct` (`group_by` is required for `having`).

```py
from sqlalchemy import func
categories = MovieCategoryScores.query.with_entities(MovieCategoryScores.category_id).filter(MovieCategoryScores.movie_id.in_([movie1, movie2])).group_by(MovieCategoryScores.category_id).having(func.count(MovieCategoryScores.category_id) > 1).all()

```

Or if you want to retrieve `Category.name` you can do the following:

```py
from sqlalchemy import func
categories = MovieCategoryScores.query.with_entities(MovieCategoryScores.name).filter(MovieCategoryScores.movie_id.in_([movie1, movie2])).group_by(MovieCategoryScores.category).having(func.count(MovieCategoryScores.category) > 1).all()

```
All right so after a lot of headaches I came up with this:

```
def get_common_categories(movie1, movie2):
    categories = MovieCategoryScores.query.with_entities(MovieCategoryScores.category_id).filter(MovieCategoryScores.movie_id.in_([movie1, movie2])).all()
    categories.sort()
    common = []
    for index, category in enumerate(categories):
        if index+1 < len(categories) and categories[index+1] == category:
            common.append(category[0])
    return common

```

Feels stupid and like there should be some way to do it only with a query and filter, but couldn't figure it out so this will have to do for now.