php – Run a SELECT JOIN with condition for different tables

Question:

I'm making a ticket system where I have the message table for each ticket . This message can be sent either by an administrator or by the user who opened the ticket .

With that, I'm the problem in time to get the user 's name, either an administrator or the user who opened the ticket because their 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 did not work, but the idea is that I select the nome or table tabela_admin or tabela_empresa according to usuario_tipo .


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

//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 are working and, as this is not really my area, I no longer know how to proceed or decide which option is the best to obtain this result and correct them.

If there is another way, something simpler or another method that is correct, it's okay to change. The important thing is to obtain the final result, which would be to obtain the name of the respective "user", be it administrator or user.

Answer:

For MySql you could make 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 the select can solve it easily.

UPDATING.

I changed the sql above to ISNULL(). Here's a working example: 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
Scroll to Top