Merge results from two tables

Asked

Viewed 205 times

0

I need to make a SELECT in two tables and unite the results.

My tables are "cursos_aula" and "cursos_progresso". In the table "cursos_aula" I have all the classes of a course. In the table "progress courses" I have record only if the client started (finishing or not) the class. That is, I need you to list all the classes of course X and display the status that is in the table "courses". If the record does not exist it should return NULL, however I am only able to unite the results that have correspondence in the two tables.

My Query current is:

SELECT a.id, a.aula, p.status
FROM cursos_aulas a
LEFT JOIN cursos_progresso p ON p.id_aula=a.id
WHERE a.id_curso='$idCurso' AND p.cpfcnpj='$cpfcnpj'
ORDER BY a.sequencia

Does anyone know what I might be doing wrong?

mysql select join

1 answer

0


In your table courses ID the primary key ? Because in the WHERE you put a.id_curso.

Try it this way:

SELECT
    a.id,
    a.aula,
    p. STATUS
FROM
    cursos_aulas a
LEFT JOIN cursos_progresso p ON p.id_aula = a.id
AND
    p.cpfcnpj = '$cpfcnpj'
WHERE
    a.id = '$idCurso'
ORDER BY
    a.sequencia

  • That’s right, the ID field is the primary key. I tested your Query here and it worked more or less... Returned all the records of the table "cursos_aulas", but in the field "status" only appeared 1 record of the table "cursos_progresso", and there are 4 records in this table.

  • I adapted your Query with mine and it worked. Instead of leaving the 2 AND in LEFT JOIN, I left only one of AND and added a WHERE. It worked. Here is the Query that worked: SELECT a.id, a.aula, p.status FROM cursos_aulas a LEFT JOIN cursos_progresso p ON p.id_aula=a. id AND p.cpfcnpj='$cpfcnpj' WHERE a.id_curso='$idCurso' ORDER BY a.sequencia

  • That’s what I was going to tell you to leave the AND only on CPF. Beauty...! Congratulations

Browser other questions tagged mysql select join

You are not signed in. Login or sign up in order to post.