Nested query with if in mysql

Question

Nested query with if in mysql

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I need to do a nested query but under one condition. You see, I have two tables.

Tabla 1-> usuarios_administradores
Tabla 2-> usuarios_colaboradores

The table that presents the inconvenience is " agenda_eventos ". Agenda_eventos has a field called " tipo ", if the type is 1, I have to get the mail from the table " usuarios_administradores ", but if "type" = 0, I have to get the email from the table " usuarios_colaboradores ".

The result I need is something like this (All the fields are from the table agenda_events, except the email, that must be obtained according to the type and the id of the user):

idevento, idempresa, idusaurio, tipousuario, titulo, emailusuario

This is my " agenda_ventos " table

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| id_empresa   | int(11)      | YES  |     | NULL    |                |
| id_usuario   | int(11)      | YES  |     | NULL    |                |
| titulo       | varchar(50)  | YES  |     | NULL    |                |
| descripcion  | varchar(200) | YES  |     | NULL    |                |
| allDay       | int(1)       | YES  |     | NULL    |                |
| inicio_fecha | date         | YES  |     | NULL    |                |
| fin_fecha    | date         | YES  |     | NULL    |                |
| inicio_hora  | time         | YES  |     | NULL    |                |
| fin_hora     | time         | YES  |     | NULL    |                |
| tipo_evento  | int(11)      | YES  |     | NULL    |                |
| tipo_usuario | int(1)       | YES  |     | NULL    |                |
| recordar     | tinyint(4)   | YES  |     | 1       |                |
+--------------+--------------+------+-----+---------+----------------+

Following this article I try to do the following, however, the result is an error. : / I do not know if there is any other recommendation for this. I would be infinitely grateful for your guidance:

    select agenda.id, agenda.id_empresa, agenda.id_usuario, agenda.tipo_usuario,  agenda.titulo, 
(select email if(agenda.tipo_usuario=1 && agenda.id_usuario=empresas_usuariosprincipales.id)from empresas_usuariosprincipales)
from agenda_eventos as agenda where recordar = 1

mysql

asked by Neftali Acosta 20.11.2018 в 21:18

source

2 answers

1

I think you can solve it in at least two ways.

One would be using LEFT OUTER JOIN with each table, where you would also put the type conditions. As for the column, you would check it with IFNULL , so it will show you either of them regardless of which table it came from:

SELECT 
    a.id, 
    a.id_empresa, 
    a.id_usuario, 
    a.tipo_usuario,  
    a.titulo,
    IFNULL(ua.email, uc.email) email
FROM agenda a
    LEFT OUTER JOIN usuarios_administradores ua ON (a.id_usuario = ua.id_usuario AND a.tipo = 1)
    LEFT OUTER JOIN usuarios_colaboradores uc ON (a.id_usuario = uc.id_usuario AND a.tipo = 0)
    WHERE a.recordar=1;

Another way to do it would be with COALESCE . It's almost the same, just change the IFNULL by COALESCE :

SELECT 
    a.id, 
    a.id_empresa, 
    a.id_usuario, 
    a.tipo_usuario,  
    a.titulo,
    COALESCE(ua.email, uc.email) email
FROM agenda a
    LEFT OUTER JOIN usuarios_administradores ua ON (a.id_usuario = ua.id_usuario AND a.tipo = 1)
    LEFT OUTER JOIN usuarios_colaboradores uc ON (a.id_usuario = uc.id_usuario AND a.tipo = 0)
    WHERE a.recordar=1;

NOTE :

In the question, some column names are not clear. In the description of the table you say that the column is called tipo_evento , but in the statement you speak of a column called tipo , which is what I used. It is also not clear how the column that serves as a link in tables ua and uc is called, I have used id_usuario . If they are not called like that, you should use the real names in both cases.

I have also used alias shorts for the tables ( a, ua, uc ), so that the query is not so cumbersome.

answered by 20.11.2018 в 21:52

last question id inserted (mysql) Web Api ASP.NET can not find the method

score 1 · Accepted Answer

Provided that in usuarios_administradores and in usuarios_colaboradores you have only one row per user, the most practical is to connect the two tables to the main query by left join . I'm going to assume, because you have not indicated, that the user IDs in the two tables is also id_usuario , to get the mail according to agenda.tipo_usuario , in addition to the left join to the two tables, we will use a case to determine where we get the email from:

select  case when agenda.tipo_usuario = 1 then adm.email when agenda.tipo_usuario = 0 then colab.email end as 'correo'
    from agenda_eventos as agenda
    left join usuarios_administradores as adm
        on agenda.id_usuario = adm.id_usuario
    left join usuarios_colaboradores as colab
        on agenda.id_usuario = colab.id_usuario
    where recordar = 1