How to get the intersection of two queries in mysql

Question

I have a table that has two columns with two foreign keys from two different tables.

this is my relationship table:

I want to choose those students who can speak both languages ​​with id 3 and 4.

How can I write a request to give me ex 12, 14

Answer 1

Suppose your relation is called "my-relation":

 SELECT R1.student_Id FROM my-Relation R1 join my-Relation R2 on R1.student_id = R2.student_id where R1.language_Id = '3' and R2.language_id = '4'

Answer 2

You can try:

SELECT 
 student_id,
 COUNT(*) total
FROM your_table 
WHERE language_id IN (3,4)
GROUP BY student_id
HAVING COUNT(*) = 2;

---

It just INdoes not guarantee that the student is involved as in the language identifier 3 & 4.

You need to use GROUP BY student_id HAVING COUNT(*) = 2to ensure that those student_idare in the results that are involved in bothlanguage id 3 & 4

---

INNER JOIN. .

SELECT 
A.student_id 
FROM your_table A 
INNER JOIN your_table B ON A.student_id = B.student_id 
  AND A.language_id = 3 AND B.language_id = 4