How i can select rows which are not present in other table?

I have three tables:

id INT

id INT
description VARCHAR

id INT
student_id (FOREIGN KEY of
class_id (FOREIGN KEY of

How i can return all classes of a student that is not in student_classes? I receive the student_id in request.params.student_id , i try something like:

async getAvailableClassesOfAStudent({ request }){
  const classes = await Database
    .leftJoin('student_classes', '', 'student_classes.class_id')
    .whereNotIn('student_classes.student_id', request.params.student_id) 

  return classes 

I’m getting:

select “class”.* from “class” left join “student_classes” on “class”.“id” = “student_classes”.“class_id” where “student_classes”.“student_id” not in $1 - syntax error at or near “$1”

1 Like

I make this way:

// Retorna as classes que um estudante pode se vincular
async getClassesDisponiveisEstudante({ request }){
  const arrayClassesVinculadasEstudante = await Database
    .where('student_classes.student_id', request.params.student_id)
    .innerJoin('class', '', 'student_classes.class_id')

  const classesDisponveis = await Classe.query()
                            .whereNotIn('id', arrayClassesVinculadasEstudante)

  return classesDisponveis 

probably have one way to do with one query, but for now it’s ok, if someone can do better, feel weelcome to post a answer