See left join with 6 tables

Question

See left join with 6 tables

Navigation

#1 by (0 votes)

1

I have 6 survey tables, form_alumnos, dimension, questions, answer_type, answer.

Table form_alumnos

and I have this query

SELECT p.id_pregunta, tr.tipo, COUNT(r.id_respuesta) as cantidad_respuestas 

                    FROM encuesta e
              LEFT JOIN form_alumnos f ON e.id_encuesta = f.id_encuesta_for
              LEFT JOIN dimension d ON e.id_encuesta = d.id_encuesta_p
              LEFT JOIN preguntas p ON d.id_dimension = p.id_dimension_p
              LEFT JOIN tipo_respuesta tr on p.id_pregunta = tr.id_pregunta 
              LEFT JOIN respuesta r on tr.id_tipo = r.id_tipo 
              WHERE f.cod_curso_alumno = '354196' AND f.anio = '2016' AND f.semestre = 'Primer'

             GROUP BY p.id_pregunta, tr.tipo

and that query throws me

the error of the query shows me all the data and I multiplies them by 6, I should show only the data of the student_code = '354196', anio = '2016', semester = 'First' and the query I want to make it should look like this:

I hope somebody can guide me, thank you in advance

mysql sql join

asked by claudia24 15.11.2017 в 22:10

source

1 answer

Help with a SQL Query pass several sqlDataSource parameters to a stored procedure

score 0 · Answer 1

The result is multiplied by each record that is associated in the other tables, you can extend the function GROUP BY to group the results as you wish, for example, if you want to group cod_curso_alumno , anio and semester , you can make the query like this:

SELECT p.id_pregunta, tr.tipo, COUNT(r.id_respuesta) as cantidad_respuestas FROM encuesta e LEFT JOIN form_alumnos f ON e.id_encuesta = f.id_encuesta_for LEFT JOIN dimension d ON e.id_encuesta = d.id_encuesta_p LEFT JOIN preguntas p ON d.id_dimension = p.id_dimension_p LEFT JOIN tipo_respuesta tr on p.id_pregunta = tr.id_pregunta LEFT JOIN respuesta r on tr.id_tipo = r.id_tipo WHERE f.cod_curso_alumno = '354196' AND f.anio = '2016' AND f.semestre = 'Primer' GROUP BY p.id_pregunta, tr.tipo, f.cod_curso_alumno, f.anio, f.semestre

Greetings.