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?

I'm not too sure about OUTER APPLY, but for the ISNULL, have you tried IS NULL (with a space)? — Infiltrator, Apr 03 '13 at 00:14
I tried but mysql IS NULL only evaluates one expression, maybe COALESCE function does the trick however I'm still stuck with the Outer Apply part — Jorge Zapata, Apr 03 '13 at 00:20
question, what are you getting from table `grupos` and `personas`? random record for each `id_credito` and `id_persona`, respectively? — John Woo, Apr 03 '13 at 00:25
id_persona always exists, however there are just some cases where id_credito is the foreign key of grupos table. If grupos relation exists I want to get G.nombre from that table, if not, take the personas table relation — Jorge Zapata, Apr 03 '13 at 00:31
why do need to call `TOP 1`? are there many `id_credito` in table `grupos` as well as `id_persona` in table `personas`? — John Woo, Apr 03 '13 at 00:37
You're right, in fact there should be just one relation. No need to use TOP — Jorge Zapata, Apr 03 '13 at 00:49

sgeddes · Accepted Answer · 2013-04-03T00:43:17.073

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.

Thank you for such a complete answer, I've tested the query and it worked. — Jorge Zapata, Apr 03 '13 at 01:24

score 1 · Answer 2 · answered Apr 03 '13 at 01:23

I think this turns it into a valid MySQL query:

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  M join
       acreditados A 
       ON A.id_acreditado = M.id_acreditado join
       creditos C 
       ON C.id_credito = A.id_credito join
       abonos AB 
       ON AB.id_movimiento = M.id_movimiento join
       (select g.id_credito, max(g.nombre) -- arbitrary nombre
        from grupos g
        group by g.id_credito
       ) tg
       on tG.id_credito = C.id_credito join
       (SELECT P.id_persona, concat_ws(' ', P.nombres, P.apellido_paterno, P.apellido_materno) AS NombComp 
        FROM   personas P 
        group by p.id_persona
       ) 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

I replaced the isnull() with coalesce() -- if you are going between databases, use standard SQL where possible. I replaced the + for concatenation with concat_ws(). Note there is a small difference . . . if one of the fields is NULL, then the MySQL value will treat it as ''.

I replaced the cross apply with an aggregate. You use top 1 without an order by, so this fetches an arbitrary row. So, this version arbitrarily chooses the maximum value.

Get column where relation is not NULL

2 Answers2