MySQL Query with LEFT JOIN (SELECT) ORDER and LIMIT

Question

Today I need help with this really long select query.

The thing is I need to get all records from 1 table "dispositivos" and then LEFT JOIN "monitoreo", some other tables, and the most important part of this query is joining the table "incidencia".

One "dispositivo" can have many "incidencia", and from all the "incidencia" i need the one where the column "alerta_id" has the lowest value, with "incidencia.fecha_incidente" = "monitoreo.fecha" and "incidencia.fecha_correccion" being NULL.

Now, since I need just one record from "incidencia" then I use LIMIT.

However I keep printing the results on the console, and when i use limit inside the select query for "incidencia" it beings the 5 columns that come from it as null, and if I don't, then it actually brings me the whole data, but of course it repeats the "dispositivos" information for each "incidencia" that it finds.

Now here's my query. And if anyone can help i'd appreciate it. If you don't understand something about my query or my previous text, don't hesitate to ask. By the way, all the quotes and plus signs is because the query is for a node.js project and i need to do that to better visualize my code.

SELECT d.id
     , d.tipo_dispositivo_id
     , d.unidad_adscripcion_id
     , d.nro_cns
     , d.cod_inter
     , d.nombre_senal
     , d.lat as lat_origen
     , d.long as long_origen
     , d.direccion_latitud_id
     , d.direccion_longitud_id
     , d.descripcion
     , d.alc
     , d.ele
     , d.cod_iala_id
     , d.sistema_acceso_id
     , d.color_bombillo_id
     , d.modelo_bombillo_id
     , d.cant_bateria
     , d.volt_grupo_bateria
     , d.nro_panel_solar
     , d.corriente_panel as cor_pan
     , d.forma_estructura
     , d.altura_torre
     , d.nro_telf
     , d.lat_gps
     , d.long_gps
     , d.activo
     , mon.tipo_evento_id
     , mon.fecha fecha_hora
     , mon.voltaje_pila
     , mon.voltaje_panel
     , mon.consumo_linterna
     , mon.corriente_panel
     , mon.estado
     , mon.temperatura
     , mon.gpsval
     , mon.lat lat_mon
     , mon.dlat
     , mon.long long_mon
     , mon.dlong
     , mon.velocidad
     , mon.rumbo
     , mon.dispositivo_id disp_check
     , i.*    
  FROM dispositivos d 
  LEFT 
  JOIN monitoreo_actual mon 
    ON mon.dispositivo_id = d.id 
  LEFT 
  JOIN 
     ( SELECT dispositivo_id disp_inc
            , alerta_id
            , fecha_incidente
            , hora
            , fecha_correccion 
         FROM incidencia inc 
        WHERE fecha_correccion IS NULL
        ORDER 
           BY alerta_id ASC LIMIT 1
     ) i
    ON i.disp_inc = d.id

Answer 1

'SELECT d.id, d.tipo_dispositivo_id, d.unidad_adscripcion_id, d.nro_cns, d.cod_inter, d.nombre_senal, d.lat as lat_origen, d.long as long_origen, '+
                              'd.direccion_latitud_id, d.direccion_longitud_id, d.descripcion, d.alc, d.ele, d.cod_iala_id, d.sistema_acceso_id, d.color_bombillo_id, '+
                              'd.modelo_bombillo_id, d.cant_bateria, d.volt_grupo_bateria, d.nro_panel_solar, d.corriente_panel as cor_pan, d.forma_estructura, d.altura_torre, '+
                              'd.nro_telf, d.lat_gps, d.long_gps, d.activo, '+
                              'mon.tipo_evento_id, mon.fecha fecha_hora, mon.voltaje_pila, mon.voltaje_panel, mon.consumo_linterna, mon.corriente_panel, mon.estado, '+
                              'mon.temperatura, mon.gpsval, mon.lat lat_mon, mon.dlat, mon.long long_mon, mon.dlong, mon.velocidad, mon.rumbo, mon.dispositivo_id disp_check, '+
                              'iala.cod, '+
                              'adsc.descripcion uni_ads_desc, '+
                              'acceso.descripcion acceso_desc, '+
                              'color.descripcion color_bombillo, '+
                              'modelo.caracteristicas, '+
                              'marca.descripcion marca_bombillo, '+
                              'evento.descripcion evento_alerta, '+
                              'inc.alerta_id, '+
                              'alert.descripcion descripcion_alerta '+
                       'FROM dispositivos d '+
                                  'LEFT JOIN monitoreo_actual mon ON mon.dispositivo_id = d.id '+
                                  'LEFT JOIN m_cod_iala iala ON iala.id = d.cod_iala_id '+
                                  'LEFT JOIN m_unidad_adscripcion adsc ON adsc.id = d.unidad_adscripcion_id '+
                                  'LEFT JOIN m_sistema_acceso acceso ON acceso.id = d.sistema_acceso_id '+
                                  'LEFT JOIN m_color_bombillo color ON color.id = d.color_bombillo_id '+
                                  'LEFT JOIN m_modelo_bombillo modelo ON modelo.id = d.modelo_bombillo_id '+
                                  'LEFT JOIN m_marca_bombillo marca ON marca.id = modelo.marca_bombillo_id '+
                                  'LEFT JOIN m_tipo_evento evento ON evento.id = mon.tipo_evento_id '+
                                  'LEFT JOIN (SELECT dispositivo_id, MIN(alerta_id) AS alerta_id, fecha_incidente, fecha_correccion '+
                                             'FROM incidencia '+
                                             'WHERE fecha_correccion IS NULL '+
                                             'GROUP BY dispositivo_id) inc ON inc.dispositivo_id = d.id AND fecha_incidente = mon.fecha '+
                                  'LEFT JOIN m_alerta alert ON alert.id = inc.alerta_id'

Answer 2

Have you tried changing your second LEFT JOIN to a JOIN?.. It sounds like you don't want the rows which don't match this section.

Answer 3

One approach to solving this problem is to use an inline view that returns the unique identifier of the rows from "incidencia" you want to return, and then join that to the inidencia table to get the rows.

e.g.

LEFT
JOIN ( SELECT l.fecha_incidente
            , MIN(l.alerta_id) AS lowest_alerta_id
       FROM incidencia l
       WHERE l.fecha_correccion IS NULL
       GROUP BY l.fecha_incidente
     ) m
  ON m.fecha_incidente = monitoreo.fecha
LEFT JOIN incidencia n
       ON n.fecha_incidente  = m.fecha_incidente
      AND n.alerta_id        = m.lowest_alerta_id

Answer 4

add in your select min(alerta_id) and group by alerta_id at the end