在mysql中获取第3个表的数据

Question

I have 3 table : events,students and parent_student_relation.

events

id | class_id | title
----------------------
1  | 16       | ABC
2  | 17       | JKL
3  | 18       | XYZ

students

id | class_id | name
----------------------
5  | 18       | StudentName1
6  | 17       | StudentName2
7  | 16       | StudentName3

parent_student_relation

student_id | parent_id
----------------------
        4  | 10
        5  | 10
        6  | 11
        7  | 11

Now i want to get data from events where i pass parent_id = 10 then get following output.

Output

id | class_id | title
----------------------
3  | 18       | XYZ

And i pass parent_id = 11 then get following output.

Output

id | class_id | title
----------------------
1  | 16       | ABC
2  | 17       | JKL

I tried with following code :

$q_student = "SELECT student_id FROM parent_student_relation WHERE parent_id = " . $parent_id;

$q_class = "SELECT class_id FROM students WHERE id IN($q_student) GROUP BY class_id";

$q = "SELECT * FROM events WHERE class_id IN($q_class)";

So i can get perfect output in $query = mysql_query($q);

But I want to all in only one join query. So how can do it?? Or its possible or not?

Answer 1

try this

$query="SELECT * 
  FROM parent_student_relation AS a 
  JOIN students AS b 
  ON a.student_id = b.id 
  JOIN events AS c 
  ON b.class_id = c.class_id 
  WHERE a.parent_id = " . $parent_id;