problem error query mysql delivery null values

Question

problem error query mysql delivery null values

Navigation

#1 by (1 votes)

0

hello I have an sql query and when the execution happens it gives me null values I do not know why they would be able to see if there is any error, all the tables have data

tables

participants
enrollment
solo_curse
generic_course
communes
institution

model

this is the query

SELECT Count(e.rut_participante) AS cantidad, 
       cg.nombre_curso, 
       cg.costo, 
       cu.id_curso_unico, 
       cu.duracion_h, 
       cu.jornada_h, 
       cu.cantidad_participantes, 
       cu.fecha_inicio, 
       cu.fecha_termino, 
       cu.hora_desde, 
       cu.hora_hasta, 
       c.nombre_comuna 
FROM   participanes p, 
       enrolamiento e, 
       curso_unico cu, 
       curso_generico cg, 
       comunas c, 
       institucion i 
WHERE  i.rut_institucion = cu.rut_institucion 
       AND p.rut_participante = e.rut_participante 
       AND c.id_comuna = cu.id_comuna 
       AND cg.id_curso = cu.id_curso 
       AND cu.id_curso_unico = e.id_curso_unico 
       AND e.id_curso_unico = 1

result Query

I need the following fields

generic_course: name_course, cost -unique_time: id_curso_unico, duration_h, día_h, amount_participants, start_date, deadline_date

-community: common_name

-institution: reason_social

-count of the rut (foreign key of participants) that is in the enrollment table

all this according to when the id of the tabla_unico equals 1

mysql sql

asked by jose miguel jara 27.03.2017 в 21:26

source

1 answer

How do I load selects with jQuery, Ajax and Codeigniter Visual Studio Error With Tables

score 1 · Answer 1

Ok, first of all, I ask you to please stop using implicit joins (because they are deprecated) and use explicit joins. On the other hand, while MySQL allows (against the standard) perform grouping functions in only some columns of your SELECT , without using a GROUP BY , it is a bad practice, because the values for those columns are arbitrary (and also because it is not standard).

I rewrote your query in the following way:

SELECT Count(Distinct e.rut_participante) AS cantidad, 
       cg.nombre_curso, 
       cg.costo, 
       cu.id_curso_unico, 
       cu.duracion_h, 
       cu.jornada_h, 
       cu.cantidad_participantes, 
       cu.fecha_inicio, 
       cu.fecha_termino, 
       cu.hora_desde, 
       cu.hora_hasta, 
       c.nombre_comuna 
FROM  participanes p
INNER JOIN enrolamiento e
    ON p.rut_participante = e.rut_participante 
INNER JOIN curso_unico cu
    ON cu.id_curso_unico = e.id_curso_unico 
INNER JOIN curso_generico cg
    ON cg.id_curso = cu.id_curso 
INNER JOIN comunas c
    ON c.id_comuna = cu.id_comuna 
INNER JOIN institucion i 
    ON i.rut_institucion = cu.rut_institucion 
WHERE  e.id_curso_unico = 1 
GROUP BY cg.nombre_curso, 
        cg.costo, 
        cu.id_curso_unico, 
        cu.duracion_h, 
        cu.jornada_h, 
        cu.cantidad_participantes, 
        cu.fecha_inicio, 
        cu.fecha_termino, 
        cu.hora_desde, 
        cu.hora_hasta, 
        c.nombre_comuna 
;

While this is not going to fix your situation that does not return data, because that happens because somewhere in the joins does not meet any of the conditions.