Home / Questions / 268965
Accepted
Victor Alfonso Pérez Espino
Asked: 2020-06-03 09:02:27 +0800 CST 2020-06-03 09:02:27 +0800 CST

How to do SQL query of 2 related tables?

  • 772

I am beginning to see DB, I am trying to understand how relationships work, the example I am trying to do consists of having a teacher table and a student table;

I want to query the students table, but with teacher data, that is, tell me to select all the students who have "teacherX" as their teacher.

My tables and relationship are like this:

Boards

Here I am doing select, but with the id that already contains the student table, what I want to know is how to indicate that, instead of putting the id, put the teacher's name something like ***select * from alumno where id_profesor = (select id from profesor where nombre = Gabriel)***(I know that I don't know how to write it that way, but I don't I know how to do it).

enter image description here

Here are the records for my 2 tables.

enter image description here

mysql

2 Answers

  1. Best Answer

    You could do it this way to stop query nesting:

    SELECT alumno.nombre, alumno.profesor
FROM   alumno, profesor
WHERE  alumno.id_profesor = profesor.id_profesor AND
       profesor.nombre = "Gabriel"

    The way you put it is also a good idea, and it would work without any problem, only that its key is called id_profesor and the name would have to go with double quotes " "...

    It would look like this:

    select * 
from   alumno
where  id_profesor = ( select id_profesor from profesor where nombre = "Gabriel" )
    • 3

  2. Use an INNER JOIN to join both tables via their primary key and foreign key:

    SELECT profesor.nombre, profesor.apellido, alumno.nombre, alumno.apellido
FROM profesor 
INNER JOIN profesor.id = alumno.id_profesor
WHERE profesor.nombre = "Gabriel";

    You should use INNER JOIN as it will search for all the records that are related in both tables by comparing the foreign key and the primary key and at the end you filter with a WHERE.

    In this way you will obtain a table with the name of the teacher and all his associated students.

    • 2