Returning Mysql Table Fields

Question

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 consulted the form table:

IdFormulario | Data |  NomeProfissional | NomeUsuario 

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

making the relationship between the professional tables and treatment, user and treatment, how to proceed to the treatment (Msc, Mr, Ms, etc.) return in the consultation? I can return only 1 treatment to 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

Answer 1

In your case you would need to realize the 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  

Noting that tp is the join between professional and treatment and tu the join between user and treatment