Untitled

use consultorio;

create table if not exists medicos(
id bigint not null primary key auto_increment,
nome varchar(256) not null
);

create table if not exists pacientes(
cpf bigint not null primary key,
nome varchar(256) not null,
idade int not null
);

create table if not exists consulta(
id bigint not null primary key auto_increment,
data datetime not null,
medicamentos boolean not null,
fk_medicos bigint,
fk_pacientes bigint,
foreign key (fk_medicos) references medicos(id),
foreign key (fk_pacientes) references pacientes(cpf)
);

insert into medicos(nome) values
('silvio'),
('santos'),
('ronaldinho'),
('kaiky');

insert into pacientes(cpf, nome, idade) values
(112313421, 'jonas', 19),
(112141595, 'lucas', 21),
(131424693, 'carlos', 41),
(134515912, 'luiz', 81);

insert into consulta(data, medicamentos, fk_pacientes, fk_medicos) values
('2023-10-28 19:30:35', true, 112313421, 2),
('2023-11-25 20:21:24', false, 131424693, 1),
('2021-12-19 19:25:30', true, 134515912, 4),
('2024-11-20 18:19:49', false, 112141595, 3);

select * from pacientes as p
inner join consulta as c_pacientes on c_pacientes.fk_pacientes = p.cpf
inner join medicos as m on c_pacientes.fk_medicos = m.id
inner join consulta as c_medicamentos on c_medicamentos.id = c_pacientes.id and c_medicamentos.medicamentos = true;