Home / Questions / 41509
Accepted
El Cóndor
Asked: 2020-12-30 05:37:17 +0800 CST 2020-12-30 05:37:17 +0800 CST

How to perform a CASE with information that does not exist in a MySQL table?

  • 772

I will put a simple example of what I want to do, I have the table Usuarioswhich is updated every 90 seconds in this example the table will have the following information:

Usuarios

Nombre    Puerto      Ip
  Juan        4      192.168.1.1
  Juan        9      192.168.1.1
  Jorge       8      192.168.1.2
  Roberto     3      192.168.1.1

But in some update you can have the following information:

Usuarios

    Nombre    Puerto      Ip
      Juan        4      192.168.1.1
      Juan        9      192.168.1.1
      Jorge       2      192.168.1.2
      Jorge       8      192.168.1.2
      Roberto     3      192.168.1.1
      Roberto     7      192.168.1.1

To know in which team each user is, I do the following query:

 Select Nombre, Puerto, Ip,
         CASE WHEN Nombre='Juan' and Puerto>5 and Ip='192.168.1.1' THEN '20' ELSE
         CASE WHEN Nombre='Juan' and Puerto<6 and Ip='192.168.1.1' THEN '19' ELSE
         CASE WHEN Nombre='Jorge' and Puerto>5 and Ip='192.168.1.2' THEN '18' ELSE
         CASE WHEN Nombre='Jorge' and Puerto<6 and Ip='192.168.1.2' THEN '17' ELSE
         CASE WHEN Nombre='Roberto' and Puerto>5 and Ip='192.168.1.1' THEN '16' ELSE
         CASE WHEN Nombre='Roberto' and Puerto<6 and Ip='192.168.1.1' THEN '15'
         END END END END END END AS Equipo
From Usuarios
Order by Usuarios Desc

My result considering the first table is as follows:

Nombre    Puerto      Ip              Equipo
  Juan        9      192.168.1.1        20 
  Juan        4      192.168.1.1        19    
  Jorge       8      192.168.1.2        18
  Roberto     3      192.168.1.1        15

As you can see, it omits Teams 17 and 16 since the case, so I want to perform a query in which I identify which teams are found without using something like this:

Nombre    Puerto      Ip              Equipo    Estado
  Juan        9      192.168.1.1        20      En_uso
  Juan        4      192.168.1.1        19      En_uso
  Jorge       8      192.168.1.2        18      En_uso
  Jorge       2      192.168.1.2        17      Sin_usar
  Roberto     7      192.168.1.1        16      Sin_usar
  Roberto     3      192.168.1.1        15      En_uso

But since the table is updated and when the equipment is not in use, its information simply does not exist in the table, I do not know how to do it.

PS: The table is a simple example so ignore the logic of the table information, the table I am working with was not made by me

mysql

1 Answers

  1. Best Answer

    Instead of doing them CASEwith a single table, put the conditions in another table and then do the comparisons. Something more or less like the following:

    SELECT Rangos.Nombre, Rangos.IP, Usuarios.Puerto, Rangos.Equipo, IF(ISNULL(Usuarios.Puerto),'Sin_usar','En_uso') Estado
FROM
(
    SELECT 'Juan' Nombre, '192.168.1.1' IP, 1 PuertoInicial, 5 PuertoFinal, '19' Equipo
    UNION ALL
    SELECT 'Juan', '192.168.1.1', 6 , 999, '20'
    UNION ALL
    SELECT 'Jorge', '192.168.1.2', 1 , 5, '17'
    UNION ALL
    SELECT 'Jorge', '192.168.1.2', 6 , 999, '18'
    UNION ALL
    SELECT 'Roberto', '192.168.1.1', 1 , 5, '15'
    UNION ALL
    SELECT 'Roberto', '192.168.1.1', 6 , 999, '16'
) Rangos LEFT JOIN Usuarios 
ON BINARY Rangos.Nombre = BINARY Usuarios.Nombre AND BINARY Rangos.IP = BINARY Usuarios.IP AND Usuarios.Puerto BETWEEN Rangos.PuertoInicial AND Rangos.PuertoFinal

    This query should return the following result:

    Nombre  IP          Puerto    Equipo  Estado
Juan    192.168.1.1 4         19      En_uso
Juan    192.168.1.1 9         20      En_uso
Jorge   192.168.1.2 8         18      En_uso
Roberto 192.168.1.1 3         15      En_uso
Jorge   192.168.1.2           17      Sin_usar
Roberto 192.168.1.1           16      Sin_usar
    • 1