Hello, I'm having some trouble getting my SQL query to work in SQLAlchemy.
In this example, I want to find out the number of meeting rooms and bathrooms available to a user. The user is part of a userGroup, which is assigned to one building. The building has many meeting rooms and bathrooms. My sql query looks like: select user.id, (select count(*) from meetingRoom where userGroup.buildingCode = meetingRoom.buildingCode), (select count(*) from restroom where userGroup.buildingCode = restroom.buildingCode) from user join userGroup on user.userGroupId = userGroup.userGroupId I've tried using subqueries: meetingRoomCount = session.query(func.count(MeetingRoom.id)).join(UserGroup, MeeingRoom.buildingId == UserGroup.buildingId).subquery() bathroomCount = session.query(func.count(Bathroom.id)).join(UserGroup, Bathroom.buildingId == UserGroup.buildingId).subquery() session.query(User.id, meetingRoomCount, bathroomCount).first() But this returns the total number of meeting rooms and bathrooms in the database, not the ones that are specific to that user. I feel like I'm missing something simple here, anyone have any ideas? -Rob -- 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.
