Returning MySql Table Fields

Question

Returning MySql Table Fields

Navigation

#1 by (1 votes)

0

Hello,

I have three tables:

profissional (id, nome, idtratamento)
usuários (id, nome idtratamento)
formulario (id, data, idprofissional,idusuario)

and another:

tratamento (id, descricao)

I need to return the following information queried from the form table:

IdFormulario | Data |  NomeProfissional | NomeUsuario 

   ...            ...       Msc. João      Sr. José

Doing the relationship between professional tables and treatment, user and treatment, how to proceed for treatment (Msc, Sr, Miss, etc.) to return in the query? I can return only 1 treatment for either professional or user. How do I return the treatments in the same sql query

SQL:

SELECT p.nome, u.nome, f.id, f.data, t.nome as tratamentouser
FROM formulario as f inner join tratamento as t on t.id = f.idusuario inner 
join profissional as p on p.id = f.idprofissional

php mysql

asked by anonymous 17.04.2017 / 18:43

1 answer

Image without quality - Safari JSF change information in XHTML

score 1 · Accepted Answer

In your case you would need to join with the treatment table 2 times, your query would look like this below:

SELECT  tp.descricao as tratamentProfissional, p.nome, tu.descricao as tratamentoUser, u.nome, f.id, f.data
FROM formulario as f 
    inner join profissional as p 
        on p.id = f.idprofissional
    inner join tratamento as tp
        on tp.id = f.idtratamento 
    inner join usuario as u 
        on p.id = f.idusuario
    inner join tratamento as tu 
        on tu.id = u.idtratamento

Observing that tp is join between professional and treatment and tu join between user and treatment