title: SQLAlchemy best way to filter a table based on values from another table
body: <p>I apologize in advance if my question is banal: I am a total beginner of SQL.</p>
  <p>I want to create a simple database, with two tables: <code>Students</code> and <code>Answers</code>.
  Basically, each student will answer three question (possible answers are <code>True</code> or <code>False</code> for each question), and his answers will be stored in <code>Answers</code> table.
  <code>Students</code> can have two &quot;experience&quot; levels: &quot;Undergraduate&quot; and &quot;Graduate&quot;.
  What is the best way to obtain all <code>Answers</code> that were given by <code>Students</code> with &quot;Graduate&quot; experience level?</p>
  <p>This is how I define SQLAlchemy classes for entries in <code>Students</code> and <code>Answers</code> tables:</p>
  <pre class="lang-py prettyprint-override"><code>import random
  
  from sqlalchemy import create_engine
  from sqlalchemy import Column, Integer, String, Date, Boolean, ForeignKey
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import sessionmaker, relationship
  
  
  db_uri = &quot;sqlite:///simple_answers.db&quot;
  db_engine = create_engine(db_uri)
  db_connect = db_engine.connect()
  
  Session = sessionmaker()
  Session.configure(bind=db_engine)
  db_session = Session()
  
  
  Base = declarative_base()    
  
  
  class Student(Base):
      __tablename__ = &quot;Students&quot;
      
      id = Column(Integer, primary_key=True)
      experience = Column(String, nullable=False)
  
  class Answer(Base):
      __tablename__ = &quot;Answers&quot;
      
      id = Column(Integer, primary_key=True)
      student_id = Column(Integer, ForeignKey(&quot;Students.id&quot;), nullable=False)
      answer = Column(Boolean, nullable=False)
  
  
  Base.metadata.create_all(db_connect)
  </code></pre>
  <p>Then, I insert some random entries in the database:</p>
  <pre class="lang-py prettyprint-override"><code>categories_experience = [&quot;Undergraduate&quot;, &quot;Graduate&quot;]
  categories_answer = [True, False]
  
  n_students = 20
  n_answers_by_each_student = 3
  random.seed(1)
  for _ in range(n_students):
      student = Student(experience=random.choice(categories_experience))
      db_session.add(student)
      db_session.commit()
      
      answers = [Answer(student_id=student.id, answer=random.choice(categories_answer))
                 for _ in range(n_answers_by_each_student)]
      db_session.add_all(answers)
      db_session.commit()
  </code></pre>
  <p>Then, I obtain <code>Student.id</code> of all &quot;Graduate&quot; students:</p>
  <pre class="lang-py prettyprint-override"><code>ids_graduates = db_session.query(Student.id).filter(Student.experience == &quot;Graduate&quot;).all()
  ids_graduates = [result.id for result in ids_graduates]
  </code></pre>
  <p>And finally, I select <code>Answers</code> from &quot;Graduate&quot; <code>Students</code> using <code>.in_</code> operator:</p>
  <pre class="lang-py prettyprint-override"><code>answers_graduates = db_session.query(Answer).filter(Answer.student_id.in_(ids_graduates)).all()
  </code></pre>
  <p>I manually checked the answers, and they are right. But, since I am a total beginner of SQL, I suspect that there is some better way to achieve the same result.</p>
  <p>Is there such an objectively &quot;best&quot; way (more Pythonic, more efficient...)? I would like to achieve my result with SQLAlchemy, possibly using the ORM interface.</p>