CODEWITHSUNDEEP
pythonsqlsqlalchemy
Gladius
Gladius

Reputation: 369

SQL Query that relies on COUNT from another table? - SQLAlchemy

I need a query that can return the records from table A that have greater than COUNT records in table B. The query needs to be able to go in line with other filters that might be applied on table A.

Example case study:

I have a person and appointments table. I am looking for all people who have been in for 5 or more appointments. It must also support extra filter statements on the person table, such as age > 18.

EDIT -- SOLUTION

subquery = db.session.query(Appointment.id_person, 
                            func.count('*').label('person_count')) \
                     .group_by(Appointment.id_person).subquery()
qry = db.session.query(Person) \
                .outerjoin((subquery, Person.id == subquery.c.id_person)) \
                .order_by(Person.id).filter(subquery.c.person_count >= 5).filter(Person.dob <= '1992-10-29')

Upvotes: 1

Views: 1647

Answers (2)

Subhash
Subhash

Reputation: 261

SELECT Person.PersonID, Person.Name
FROM Person INNER JOIN Appointment
ON Person.PersonID = Appointment.PersonID
GROUP BY Person.PersonID, Person.Name
HAVING COUNT(Person.PersonID) >= 5

Upvotes: 0

Kevin Stricker
Kevin Stricker

Reputation: 17388

Use a subquery:

SELECT * from person
WHERE PersonID IN 
  (SELECT PersonId FROM appointments
   GROUP BY PersonId
   HAVING COUNT(*) >= 5)
AND dob > 25

Upvotes: 1

Related Questions