Run a SELECT JOIN with conditions for different tables

Question

I’m making a system of ticket where I have the message table of each ticket. This message can be sent by both an administrator and the user who opened the ticket.

With this, I have the problem when getting the user name, be it an administrator or the user who opened the ticket, because your records are in different tables (tabela_admin, tabela_usuario).

---

The first option I tried to use was the following:

//Estrutura da tabela ticket_mensagem
id  |  ticket_id  |  usuario_id  |  usuario_tipo  |  data_criado

//outro sql obtêm os dados gerais do ticket
SELECT a.*, b.nome
FROM ticket_mensagem a 
LEFT JOIN tabela_admin   b ON a.usuario_id = b.id AND a.usuario_tipo = 1
LEFT JOIN tabela_usuario b ON a.usuario_id = b.id AND a.usuario_tipo = 2
WHERE a.ticket_id = '$ticket_id'

Obviously it didn’t work, but the idea is that I select the nome or the table tabela_admin or tabela_empresa in accordance with the usuario_tipo.

---

The second option, would be to save in the ticket_message table or the admin or company id, leaving the other as null, follows example:

//Estrutura da tabela ticket_mensagem
id  |  ticket_id  |  admin_id  |  usuario_id  |  data_criado
1   |      1      |    NULL    |     2        |   .....
2   |      1      |    4       |     NULL     |   .....

//outro sql obtêm os dados gerais do ticket
SELECT a.*, b.nome, c.nome
FROM chamado_msg a 
LEFT JOIN tabela_admin   b ON a.admin_id = b.id
LEFT JOIN tabela_usuario c ON a.usuario_id = c.id
WHERE a.id_ticket = '$id_ticket'

---

The problem is that none of the options is working and, as this is not my area, I don’t know how to proceed or decide which of the options is the best to get this result and correct them.

If there is another way, something simpler or another method that is correct, there is no problem in changing. The important thing is to obtain the final result, which would obtain the name of the respective "user", be it administrator or user.

Answer 1

Make a CASE:

SELECT ticket_mensagem.*, 
       CASE WHEN ticket_mensagem.usuario_tipo = 1
            THEN tabela_admin.nome
            ELSE tabela_usuario.nome
       END  AS nome
FROM ticket_mensagem  
LEFT JOIN tabela_admin   ON tabela_admin.id = ticket_mensagem.usuario_id
                         AND ticket_mensagem.usuario_tipo = 1
LEFT JOIN tabela_usuario ON tabela_usuario.id = ticket_mensagem.usuario_id
                         AND ticket_mensagem.usuario_tipo = 2
WHERE ticket_mensagem.ticket_id = '$ticket_id'

Answer 2

For Mysql you could do an IF in Select:

IF(ISNULL(b.nome), c.nome, b.nome) as nome

I’m not absolutely sure about the syntax, but the IF in select can solve it easily.

UPDATING.

I changed the above sql to ISNULL(). Here’s an example working: Fiddler

CREATE TABLE cliente (id INT NOT NULL AUTO_INCREMENT,
                      nome varchar(20) default null,
                      PRIMARY KEY (id)
                      );

CREATE TABLE administrador (id INT NOT NULL AUTO_INCREMENT,
                      nome varchar(20) default null,
                      PRIMARY KEY (id)
                      );

INSERT INTO cliente(id, nome) values (1, null);
INSERT INTO cliente(id, nome) values  (2, "Ricardo");

INSERT INTO administrador(id, nome) values (1, "Daniel");
INSERT INTO administrador(id, nome) values  (2, null);


select 
IF(ISNULL(c.nome), a.nome, c.nome) as nome
from cliente c,
administrador a
where c.id = a.id
and c.id = 1

Answer 3

After some tests and some attempts based on the first 2 answers, since none solved the problem directly, only guided me to the final result, this was the final code I used and ended up solving my problem:

SELECT 
    a.*,    
    IF(a.tp_usuario = 1,  b.nome, c.nome) as 'nome'
FROM ticket_mensagem a, tabela_admin b, tabela_empresa c
WHERE a.id_ticket = '$id_ticket'

The problem of using the CASE is that it returned ALL data from the tables *_adminor *_empresa, while I only needed the name.

Already the problem in the answer with the use of if is that he didn’t take the nome admin or the nome empresa. He only took the nome admin or the nome empresa, but only if the nome admin were null.

That’s not the check I wanted.

I needed to get the nome admin if the tp_usuario was 1, or take the nome empresa if the tp_usuario were 2. Therefore my final answer is working within those requirements.