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

问题描述：

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?

我有3个表：events，students和parent_student_relation。 p>

事件 strong> p>

  id |  class_id |  title 
 ---------------------- 
1 |  16 |  ABC 
2 |  17 |  JKL 
3 |  18 |  XYZ 
  code>  pre> 
 
  学生 strong>  p> 
 
 
  id |  class_id |  name 
 ---------------------- 
5 |  18 |  StudentName1 
6 |  17 |  StudentName2 
7 |  16 |  StudentName3 
  code>  pre> 
 
   parent_student_relation  strong>  p> 
 
 
  student_id |  parent_id 
 ---------------------- 
 4 |  10 
 5 |  10 
 6 |  11 
 7 |  11 
  code>  pre> 
 
 现在我想从 events  code>获取数据，其中我传递 parent_id = 10  code>然后得到以下输出 。 p> 
 
 
 输出 strong>  p> 
 
 
  id |  class_id |  title 
 ---------------------- 
3 |  18 |  XYZ 
  code>  pre> 
 
 我传递 parent_id = 11  code>然后得到以下输出。 p> 
 
 
  输出 strong>  p> 
 
 
  id |  class_id |  title 
 ---------------------- 
1 |  16 |  ABC 
2 |  17 |  JKL 
  code>  pre> 
 
 我尝试使用以下代码： p> 
 
 
  $ 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）”; 
   code>  pre> 
 
 所以我可以在 $ query = mysql_query（$ q）;  code>  p> 
 
 
中得到完美的输出但是 我想只在一个连接查询中。 那怎么办呢？ 或者它可能与否？ p> 
  div>

答

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;

答

Use this:

$query="select e.id,e.class_id,e.title,s.id from event e 
           inner join student s on e.class_id=s.class_id
           inner join parent_student_relation p on s.id=p.student_id
            where p.parent_id=$parent_id";

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

相关推荐