Execute a SELECT JOIN with condition for different tables

Question

Execute a SELECT JOIN with condition for different tables

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

3

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

With this, I have the problem of getting the user name, be it an administrator or the user who opened 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 nome or table tabela_admin or tabela_empresa according to usuario_tipo .

The second option would be to save the table ticket_message or the id of admin or company, 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 are working, and since this is not my area very much, I do not know how to proceed or decide which option is best to get this result and correct them.

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

php sql

asked by anonymous 26.08.2016 / 15:25

3 answers

1

For MySql you could do an IF in Select:

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

I'm not entirely sure about the syntax, but the IF on 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

26.08.2016 / 15:30

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'

26.08.2016 / 15:36

Android, how to do security in JSON? Filter with first item from a ManyToMany Django

score 0 · Accepted Answer

After some tests and some attempts based on the first 2 answers, since none solved the problem directly, they only guided me to the final result, this was the final code that I used and solved 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 with using CASE is that it returns ALL of the data in the *_admin or *_empresa tables, whereas I just needed the name.

The problem with the response to if is that it did not catch nome admin or nome empresa . It would take only nome admin or nome empresa , but only if nome admin was null.

That was not the check I wanted.

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