MySQL – Can’t Select Two Tables at the Same Time

MySQLsyntax

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);

Best Answer

Based on your tables, you may be trying for the following:

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

Note: your case statement will need a value filled in.

Related Question