Get left table fields if right join is null on MySQL

Question

i have this query:

SELECT 
a.nombre,
a.apellidos,
p.nombre AS prueba,
IF(r.puntuacion IS NULL, 0, 1) AS prueba_realizada,
r.puntuacion,
r.id
FROM alumnos a
LEFT JOIN resultados r ON r.alumno_id = a.id
RIGHT JOIN pruebas p ON p.id = r.prueba_id

And i have this result:

I need to get the "nombre" and "apellidos" field in the RIGHT JOIN result from the "alumnos" table, because the goal for the query is to get all the "pruebas" that the user has not done and those it has already done ...

What i am doing wrong?

Here is the db schema

This is the expected result

Can you show what the expected output might look like? Will it have all the tests and students? — clinomaniac, Dec 18 '17 at 21:28
I have updated the post with the expected result. I want to return all the tests for a student, those already done and the not done ... — viher, Dec 18 '17 at 21:38
You cannot get the name for a test which has not been taken. You can use a cross join to get all possible combinations for names and tests but the last row is not doable without complicated manipulations since there is no test taken. Start with pruebas table since you need all rows from that and try to do a CROSS join with alumnos. — clinomaniac, Dec 18 '17 at 21:44

score 0 · Answer 1 · answered Dec 18 '17 at 19:41

0

You are doing the RIGHT JOIN on pruebas which has no row can you RIGHT JOIN pruebas on alumnos? some like a.id= p.id

SELECT 
a.nombre,
a.apellidos,
p.nombre AS prueba,
IF(r.puntuacion IS NULL, 0, 1) AS prueba_realizada,
r.puntuacion,
r.id
FROM alumnos a
LEFT JOIN resultados r ON r.alumno_id = a.id
RIGHT JOIN pruebas p ON p.id = a.id

answered Dec 18 '17 at 19:41

R.Laney

174
8

But "pruebas" table has not foreign key with "alumnos" table ... here is the schema: https://image.ibb.co/jGBzQR/q2.png – viher Dec 18 '17 at 19:50

score 0 · Accepted Answer · answered Dec 18 '17 at 22:03

Can you try to use this query:

SELECT pa.nombre, pa.apellidos, pa.prueba,
IF(r.puntuacion IS NULL, 0, 1) AS prueba_realizada,
r.puntuacion,
r.id
FROM 
resultados r RIGHT JOIN
(SELECT a.id alumno_id,
 a.nombre,
a.apellidos,
p.id prueba_id,
p.nombre AS prueba
FROM pruebas p CROSS JOIN alumnos a) pa
ON r.alumno_id = pa.alumno_id 
AND r.prueba_id = pa.prueba_id;

Cross join gets all possible combinations then checks to see what exists in the results table.

Yeah! That's it. Thank you so much! – viher Dec 18 '17 at 22:06 — viher, Dec 18 '17 at 22:06

Get left table fields if right join is null on MySQL

2 Answers2