Home / Questions / 280716
Accepted
Fabian
Asked: 2020-07-23 10:38:22 +0800 CST 2020-07-23 10:38:22 +0800 CST

Help with a SQL Query

  • 772

I have 2 tables, 1 of students and another called student_registration

I need to make a select from the students table, get their id and with that id enter the student_matricula table and show the enrollment number

the students table is like this:

id string,
nombres string,
direccion string,
edad int

and the student_registration table is like this

alumno_id,
numero_matricula,
fecha_matricula

I need to show the name of the student and his registration number

for instance

David 2832-1234

I tried to do it with a select from join but it doesn't work this way

SELECT e.nombres, e.id 
FROM alumnos e JOIN alumno_matricula e2 SELECT e2.numero_matricula WHERE e.id = e2.alumno_id
mysql

4 Answers

  1. Best Answer

    hi can you use this 

    SELECT e.nombres, e.id, e2.numero_matricula
    FROM alumnos e INNER JOIN alumno_matricula e2 ON e.id = e2.alumno_id

    With this you match the id and you can access the value of the enrollment, in case the student does not have enrollment you could use something like this to show the value in NULL

    SELECT e.nombres, e.id, e2.numero_matricula 
FROM alumnos e LEFT JOIN alumno_matricula e2 ON e.id = e2.alumno_id

    And if you wanted to show something specific instead of null it could be

    SELECT e.nombres, e.id, COALESCE(e2.numero_matricula ,'Sin matricula')
FROM alumnos e LEFT JOIN alumno_matricula e2 ON e.id = e2.alumno_id
    • 4

  2. Use one INNER JOINlike this:

    SELECT alumnos.nombres, alumno_matricula.numero_matricula
FROM alumnos
JOIN alumno_matricula ON alumnos.id = alumno_matriculas.alumno_id;

    Explanation

    • Where to obtain the data from the tables, you do it through the columns that relate them, which are: primary key (students.id) and foreign key (registration_students.student_id)
    • You don't need to do 2 times SELECTto get the data, within it you can only do it by writing tablaNombre1.columna,tablaNombre2.columna
    • INNER JOINor just JOINwhat it does is get the data from both tables, where the record on the left (id) matches the record on the right (student_id) and discarding the others

    I notice in your comments that you also want those that do not have tuition; then you must use LEFT JOINso that the result considers both those records that coincide in both tables and those records on the left side (students) that have no link with the table on the right (matricula_alumnos)

    Reference sources

    • 1

  3. You have to indicate what type of JOIN it is, there are 3 types, LEFT JOIN, INNER JOIN, RIGHT JOINto understand each type I recommend you search for information about each one on the internet, An example . In this case it is required INNER JOIN, the JOIN condition must be indicated with ON, and the select of both tables goes in a single SELECT, not in two, so your query would be

    SELECT e.nombres, e.id e2.numero_matricula
FROM alumnos e 
INNER JOIN alumno_matricula e2 ON e.id = e2.alumno_id
    • 1

  4. Normally, JOINs are a bit complicated for me, that's why I usually use plain instructions... Something like this should work 100%

       SELECT nombres, id, numero_matricula FROM alumnos, alumno_matricula WHERE alumnos.id= alumno_matricula.alumno_id
    • 0