I am creating a database for my work, and it’s hard for me to figure out how to build this query.
Tables related to my problem:
Work
Surgical_Planning - has work as a foreign key, exists for some tasks, not for others
Medical_Model - has work as a foreign key, from 1 to 1 relationships with work
This is a work request in which I have no information about surgical planning.
SELECT job, physician_idphysician as Physician, patient_idpatient as Patient, status, DATE_FORMAT(scan_date, '%M %e, %Y, %l:%i%p') as Scan_Date, DATE_FORMAT(timestamp, '%M %e, %Y, %l:%i%p') as Recieved, DATE_FORMAT(date_required, '%M %e, %Y, %l:%i%p') as Date_Required FROM job, patient_has_physician as phys, Scan, Medical_Model as med WHERE Scan.job_job = job AND phys.job_job = job AND med.job_job = job AND job.type = 'medical
I think I want to make a left join so that it displays each job in order with all the information in the query above, but then when there is Surgical_Planning for job #, I want there to be a column for that too. Here is my attempt that does not work.
SELECT job, physician_idphysician as Physician, patient_idpatient as Patient, status, DATE_FORMAT(scan_date, '%M %e, %Y, %l:%i%p') as Scan_Date, DATE_FORMAT(timestamp, '%M %e, %Y, %l:%i%p') as Recieved, DATE_FORMAT(date_required, '%M %e, %Y, %l:%i%p') as Date_Required FROM job, patient_has_physician as phys, Scan, Medical_Model as med LEFT JOIN Surgical_Planning ON job.job = Surgical_Planning.job_job AND Scan.job_job = job AND phys.job_job = job AND med.job_job = job AND job.type = 'medical'
I can make this main left join work the way I want, but if I want to add more columns, like the ones above, this will not work.
SELECT job, planning_id FROM job LEFT JOIN Surgical_Planning ON job = Surgical_Planning.job_job
Can I use a subquery? I can figure out these simpler queries, but really have problems with these more complex joins and subqueries. Any advice is appreciated.
EDIT --- Operation Table Schema
- Table mmrl . job
TABLE DROP IF EXISTS mmrl . job ;
CREATE A TABLE IF mmrl DOES NOT EXIST mmrl . job (
job INT (11) NOT NULL AUTO_INCREMENT,
type VARCHAR (45) NULL,
status VARCHAR (45) NULL DEFAULT NULL,
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ( job ))
ENGINE = InnoDB
DEFAULT PARAMETER = latin1;