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
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 (
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_empresa according to
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.
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.
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