Can't select 2 tables at the same time I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and diagnostic.

I got to select the conDate, Patient Name (from table 'ppl'), Medic Name (also from 'ppl') and Diagnostic but I am having trouble for hours doing this. I am very noob at MySQL but I am trying to learn. May anyone help me with this logic problem? 

I tried many methods.

select conDat, pa.pesId = pacientes.pacId, me.pesId = medicos.medId, conDiag, case when null then 'Indefinido' end
    from consultas
    inner join pessoas as pa on pa.pesId = pacientes.pacId
    inner join pessoas as me on me.pesId = medicos.medId;


Thank you very much 

This is my creating tables code:

create table consultas (

conId int primary key auto_increment,
conDat date not null,
pacId int not null,
medId int not null,
conDiag varchar (30) );

create table pessoas (

pesId int primary key auto_increment,
pesNome varchar (50) not null);

create table pacientes (

pacId int primary key default 1,
pacTel varchar (20) not null,
pacAtiv bool);

create table medicos (

medId int primary key default 1,
medCPF varchar (20) not null,
medAtiv bool);
If I understand your specifications well, and although I haven't tested this query in the server yet, it should work:

SELECT CO.conDat, 
       PA.pesNome                         AS paciente, 
       ME.pesNome                         AS medico,
       COALESCE(CO.conDiag, 'indefinido') AS condiag
    FROM  consultas    CO
    INNER JOIN pessoas PA 
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;


Since this query JOINS the table consultas with pessoas twice in order to retrieve the pessoas.pesNome column values related to medicos and pacientes respectively, it is important that you look with special attention to the aliases given to the PA.pesNome and ME.pesNome columns (by way of the AS keyword in the SELECT statement), because such aliases are used for the purpose of displaying different column headers in the obtained result set, and thus, ambiguities are avoided, since the meaning of each column, paciente or medico, is clear.

For handling the cases when consultas.conDiag contains NULL, the above query takes advantage of the COALESCE() function which, in this particular case, substitutes NULL values in said column with your custom defined string ‘indefinido’.

Alternately, you can try this other query, which shows a different method for replacing the consultas.conDiag column value when it holds NULL, via the IFNULL() function:

SELECT CO.conDat, 
       PA.pesNome                       AS paciente, 
       ME.pesNome                       AS medico,
       IFNULL(CO.conDiag, 'indefinido') AS conDiag 
    FROM  consultas    CO
    INNER JOIN pessoas PA 
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;