How do i get the ID from parent table and JOIN from child table to run the query.

manpreet Best Answer 3 years ago

I have two tables, one is students and another is schools. I am using a FK to call school id ( scid ) to the students table.

Students

+------+---------+-----+-----------+
| stid | student | age | school_id |
+------+---------+-----+-----------+
|  1   | John    |  26 |         2 |
|  2   | Susan   |  24 |         1 |
+------+---------+-----+-----------+

schools

+------+------------+-----------+
| scid | schoolname | syllabus  |
+------+------------+-----------+
|  1   | school1    | syllabus1 |
|  2   | school2    | syllabus2 |
+------+------------+-----------+

The view page displays all the details where i am using a id to get the details from the students table and using a join to get the information from school table.

Student name: John
Age: 26
School: School 2

I need to use the id for students and use the join to get the name from the school. How do i do that. I came up with this, but how do i use the id with this.

SELECT schools.name
FROM schools
    LEFT JOIN students
        ON students.school_id=schools.scid;