Get column where relation is not NULL

Question

I have a database which was migrated from SQL Server to MySQL. I had an existing query in SQL Server to get the value of a column where the relation is not null, I mean, if the relation between two tables is null then it means that there should be a relation with another table.

This is the query I was using:

SELECT C.expediente, 
       C.status, 
       Sum(M.monto)                             AS monto, 
       Sum(M.interes)                           AS interes, 
       Sum(M.iva)                               AS iva, 
       Sum(M.capital)                           AS capital, 
       M.fecha_mov, 
       AB.tipo_abono, 
       AB.id_deposito, 
       Isnull(Min(tg.nombre), Min(tp.nombcomp)) AS nombreGrupo 
FROM   movimientos AS M 
       JOIN acreditados AS A 
         ON A.id_acreditado = M.id_acreditado 
       JOIN creditos AS C 
         ON C.id_credito = A.id_credito 
       JOIN abonos AS AB 
         ON AB.id_movimiento = M.id_movimiento 
       OUTER apply (SELECT TOP 1 G.nombre 
                    FROM   grupos G 
                    WHERE  G.id_credito = C.id_credito) tg 
       OUTER apply (SELECT TOP 1 P.nombres + ' ' + P.apellido_paterno + ' ' 
                                 + P.apellido_materno AS NombComp 
                    FROM   personas P 
                    WHERE  A.id_persona = P.id_persona) tp 
GROUP  BY M.fecha_mov, 
          AB.tipo_abono, 
          AB.id_deposito, 
          C.expediente, 
          C.status 
ORDER  BY M.fecha_mov 

But it seems that MySQL has no OUTER APPLY or ISNULL. How can I translate this query to MySQL?

Answer 1

You have a couple of issues to review:

1 - The APPLY operator is not supported in MySQL. However, for your given usage, it looks like you can probably just use a LEFT OUTER JOIN.

2 - MySQL does not support TOP. Instead, use LIMIT. However, I don't think you need it in this case since you are using MIN of each of those fields.

3 - To concatenate strings in MySQL, use CONCAT vs "+".

4 - Finally, I prefer using COALESCE in MySQL to check for NULLs. There are other options though.

So putting it all together, this should be close (untested):

SELECT C.expediente, 
       C.status, 
       Sum(M.monto)                             AS monto, 
       Sum(M.interes)                           AS interes, 
       Sum(M.iva)                               AS iva, 
       Sum(M.capital)                           AS capital, 
       M.fecha_mov, 
       AB.tipo_abono, 
       AB.id_deposito, 
       COALESCE(MIN(tg.nombre), MIN(tp.nombcomp)) AS nombreGrupo 
FROM   movimientos AS M 
       JOIN acreditados AS A 
         ON A.id_acreditado = M.id_acreditado 
       JOIN creditos AS C 
         ON C.id_credito = A.id_credito 
       JOIN abonos AS AB 
         ON AB.id_movimiento = M.id_movimiento 
       LEFT OUTER JOIN (
            SELECT id_credito, nombre 
            FROM   grupos
            ) tg ON tg.id_credito = C.id_credito 
       LEFT OUTER JOIN (
            SELECT id_persona, CONCAT(nombres,' ',apellido_paterno,' ',apellido_materno) AS NombComp 
            FROM   personas 
            ) tp ON A.id_persona = tp.id_persona
GROUP  BY M.fecha_mov, 
          AB.tipo_abono, 
          AB.id_deposito, 
          C.expediente, 
          C.status 
ORDER  BY M.fecha_mov 

You may not need some of those fields in your GROUP BY -- just your DISTINCT fields needed.