Syntax error while using WHERE clause in PostgreSQL

I’m trying to run some queries related to the University database in PostgreSQL. To find students advised by instructors from different departments, I used-

select distinct s_id from advisor
where (s_id ,i_id) in (select distinct student.id,instructor.id from
student join instructor where student.dept_name <> instructor.dept_name);

However im getting the following error:

ERROR: syntax error at or near “where”

LINE 3: student join instructor where student.dept_name <> instructo…

Any fix?

Answer

Please try this:

select distinct s_id from advisor
where (s_id ,i_id) in (select distinct student.id,instructor.id from
student join instructor on student.dept_name <> instructor.dept_name);

Instead of where you need to use on clause with join.