Select first Null record in query LEFT JOIN

-1

I am doing a system to carry out courses.

I have 2 tables, one of name "Index" and another "Completed", in "Index", I keep the index of the course (ie the curriculum, units, exams, etc) and "Completed" the id of the user and the id of the index table that is already complete. Then one in a query as follows:

select * from indice_cursos a left join completado b on a.Id=b.id_completado and b.id_usuario=$id_usuario where a.id_curso=$id_curso

What I need is to get the first record of the index that the user has not yet completed.

I attach images of the tables

Table Index:

Table completed:

Left Join Query:

How should the consultation be? Thanks !!!

    
asked by Patricio 02.05.2017 в 19:50
source

2 answers

0

If I understood correctly, what you are looking for is to obtain the first record of the indice table that the user has not yet completed.

For this you simply must:

  • Indicate that you want those where the completado.id_usuario is NULL
  • Limit the number of records to one ( 1 )
  • Optional : Sort the records according to the desired criteria ( eg: el primero ordenados por indice.id ascendentemente ).

Example:

SELECT * 
FROM indice_cursos AS a
LEFT JOIN completado AS b
  ON a.Id = b.id_completado 
  AND b.id_usuario = $id_usuario 
WHERE a.id_curso = $id_curso 
  AND b.id_usuario IS NULL
ORDER BY a.id ASC
LIMIT 1
    
answered by 02.05.2017 / 21:30
source
0

Simply add the condition to find the NULLs. And if you only want the first record you find, put a LIMIT.

By tradition and readability, the words of the SQL language are usually capitalized

SELECT * FROM indice_cursos a 
LEFT JOIN completado b ON a.Id=b.id_completado 
WHERE  ( b.id_usuario=$id_usuario  
    AND a.id_curso=$id_curso )
    AND b.id_completado IS NULL
LIMIT 1

On the other hand, the normal thing is that there is only one condition in the JOIN, since the relationship between tables is usually only one field, the other condition should be in the WHERE, and in your question it is not clear which is the relationship between tables.

    
answered by 02.05.2017 в 21:38