Hello all, I'm writing a SA-based library to work with an existing MSSQL database project. There is one query in particular I haven't been able to figure out how to express in SA: I have a many to many relationship between schools and coordinators and I want to find the schools that have more than one coordinator.
>From my models.py: class School(Base): __tablename__ = 'School' id = Column('Id', Integer, primary_key=True) school_name = Column('SchoolName', String) school_coordinators = Table('SchoolCoordinator', Base.metadata, Column('SchoolId', Integer, ForeignKey('School.Id')), Column('CoordinatorId', Integer, ForeignKey('Coordinator.Id')) ) class Coordinator(Base): __tablename__ = 'Coordinator' id = Column('Id', Integer, primary_key=True) email_address = ('EmailAddress', String) first_name = Column('FirstName', String) last_name = Column('LastName', String) schools = relationship('School', secondary=school_coordinators, backref='coordinators') And I want to achieve SQL that basically looks like this (IE, show me the schools with more than one coordinator): SELECT s.Id, COUNT(c.Id) FROM School s INNER JOIN SchoolCoordinator sc ON s.Id = sc.SchoolId INNER JOIN Coordinator c ON sc.CoordinatorId = c.Id GROUP BY s.Id HAVING COUNT(c.Id) > 1 The HAVING example in the query object documentation is for a one-to-many relationship and seems pretty straightforward. None of the incantations I've tried have worked for a m2m relationship however. Any advice is much appreciated. Thanks, Matthew -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.