SQLize | PHPize | SQLtest

CREATE TABLE SpecialityDoctors( id int not null primary key, name varchar(256), ); CREATE TABLE Doctors( id int not null primary key, fullName varchar(256), specialityId int not null CONSTRAINT FK_Doctors_SpecialityDoctors FOREIGN KEY (specialityId) REFERENCES SpecialityDoctors(id) ); CREATE TABLE Patients( id int not null primary key, fullName varchar(256) ); CREATE TABLE Preferences( id int not null primary key, IdSpeciality int not null, IdPatient int not null, Preference varchar(512) CONSTRAINT FK_Preference_SpecialityDoctors FOREIGN KEY (IdSpeciality) REFERENCES SpecialityDoctors(id) , CONSTRAINT FK_Preference_Patients FOREIGN KEY (IdPatient) REFERENCES Patients(id) ); CREATE TABLE Appointment( id int not null primary key, IdDoctor int not null, IdPatient int not null, apTime datetime not null, CONSTRAINT FK_Appointment_Doctors FOREIGN KEY (IdDoctor) REFERENCES Doctors(id) , CONSTRAINT FK_Appointment_Patients FOREIGN KEY (IdPatient) REFERENCES Patients(id) ); INSERT INTO SpecialityDoctors (id, name) VALUES (1, 'Cardiologist'), (2, 'Dermatologist'), (3, 'Endocrinologist'); -- -- INSERT INTO Doctors -- INSERT INTO Doctors (id, fullName, specialityId) -- VALUES -- (1, 'John Smith', 1), -- (2, 'Anna Johnson', 2), -- (3, 'Michael Davis', 3); -- -- INSERT INTO Patients -- INSERT INTO Patients (id, fullName) -- VALUES -- (1, 'Emily Brown'), -- (2, 'Daniel Wilson'), -- (3, 'Sophia Martinez'); -- -- INSERT INTO Preference -- INSERT INTO Preferences (Id, IdSpeciality, IdPatient, Preference) -- VALUES -- (1, 1, 1, 'Afraid of injections '), -- (2, 2, 2, 'I have soft skin,'), -- (3, 3, 3, 'I will only go to a woman'); -- -- INSERT INTO Appointment -- INSERT INTO Appointment (Id, IdDoctor, IdPatient, "Time") -- VALUES -- (1, 1, 1, '2023-05-17 10:00:00'), -- (2, 2, 2, '2023-05-18 14:30:00'), -- (3, 3, 3, '2023-05-19 09:45:00'); SELECT p.fullName AS PatientName, d.fullName AS DoctorName, s.name AS Specialty, a.apTime AS AppointmentTime, pr.Preference as Preference FROM Appointment a JOIN Doctors d ON d.id = a.IdDoctor JOIN Patients p ON p.id = a.IdPatient JOIN Preferences pr ON pr.IdPatient = p.id AND pr.IdSpeciality = d.specialityId JOIN SpecialityDoctors s ON s.id = pr.IdSpeciality;