SQL IN doesn't work to filter table So I have 'students' with the following attributes:
student_id, name, mentor, district_id. Where district_id is a foreign key. Mentors are also students, so mentor refers to a student_id.
And I have 'districts' with the following attributes:
district_id, country.
I want to get students who are from a specific country, whos mentors are from a specific country.
Here's how I get the students from specific country:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = &quot;Japan&quot;;

I do the exact same thing but with the intention of using it to filter out by mentors of students.
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = &quot;USA&quot;;

So now I have two tables one for students from Japan, and one for students from the USA. To get Japanese students with American mentors I would 'combine' these two tables like this:
SELECT student_id,
FROM districts, students
Where students.district_id = districts.district_id
AND district.country = &quot;Japan&quot;
AND students.mentor in (
    SELECT student_id,
    FROM districts, students
    Where students.district_id = districts.district_id
    AND district.country = &quot;USA&quot;
);

The idea is that I check if the studentID given by students.mentor is in the table of studentID of people from the US.
However this doesn't work. Only some are filtered off. I checked by adding students.mentor to the SELECT in the first line. And comparing it to the IDs in the second table.
Here is an example of the what the tables would look like with the added check.
Table 1) Students in Japan:
100
101
102

Table 2) Students in USA:
201
202
203

Table 3) Students in Japan with USA mentors (wrong output)
101 201
102 301
103 789

Table 3) Desired output
101 201
You need to join the mentors to the students, which translates here to a self join of students and then join the districts twice, once for the students and once for the mentors. Then you can filter by the countries.
SELECT ss.student_id
       FROM students ss
            INNER JOIN students sm
                       ON sm.student_id = ss.mentor
            INNER JOIN districs ds
                       ON ds.district_id = ss.district_id
            INNER JOIN districts dm
                       ON dm.district_id = sm.district_id
       WHERE ds.name = '&lt;the country the students should be from&gt;'
             AND dm.name = '&lt;the country the mentors should be from&gt;';

Some notes:

It is recommended to use modern, explicit JOIN syntax over old style joins with a comma separated list in the FROM clause. The former are easier to write without errors, to read and understand and maintain.
Do not use double quotes for string literals. In SQL string literals are quoted using single quotes. MySQL does accept double quotes but your code will be more portable when you use single quotes. And you'll get used to it and aren't in for surprises should you write a query for another DBMS.
In this case the question was easy enough to understand without it. But next time make sure to include sample input, i.e. sample data for the tables you work with, preferably as INSERT statements. Your current result as well as the desired result is absolutely meaningless without it. Of course you should also include your desired result. But never omit sample data, if you want to help those who try to help you.