title: mysql left join with multiple columns
body: <p>I'm building a database for my work and I'm having trouble figuring out how to build this query.</p>
  
  <p>The tables relevant to my problem are:</p>
  
  <p>job</p>
  
  <p>Surgical_Planning - has job as foreign key, exists for some jobs, doesn't for others</p>
  
  <p>Medical_Model - has job as foreign key, 1 to 1 relationship with job</p>
  
  <p>This is a working query where I don't have any information about the surgical planning</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>I think I want to do a Left join so that it will display every job in order, with all the information in the query above, but then when there is a Surgical_Planning for a job # I want there to be a column for that as well. Here is my attempt that is not working</p>
  
  <pre><code>    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'
  </code></pre>
  
  <p>I can get this basic left join working the way I want, but if I want to add more columns like above it doesn't work.</p>
  
  <pre><code>    SELECT job, planning_id
      FROM job
      LEFT JOIN Surgical_Planning ON job = Surgical_Planning.job_job
  </code></pre>
  
  <p>could a subquery be used as well? I can figure out these more basic queries but really have trouble with these more complex join and subquery ones. any advice is appreciated.</p>
  
  <p>EDIT --- Job table schema</p>
  
  <hr>
  
  <p>-- Table <code>mmrl</code>.<code>job</code></p>
  
  <hr>
  
  <p>DROP TABLE IF EXISTS <code>mmrl</code>.<code>job</code> ;</p>
  
  <p>CREATE  TABLE IF NOT EXISTS <code>mmrl</code>.<code>job</code> (</p>
  
  <p><code>job</code> INT(11) NOT NULL AUTO_INCREMENT ,</p>
  
  <p><code>type</code> VARCHAR(45) NULL ,</p>
  
  <p><code>status</code> VARCHAR(45) NULL DEFAULT NULL ,</p>
  
  <p><code>timestamp</code> TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,</p>
  
  <p>PRIMARY KEY (<code>job</code>) )</p>
  
  <p>ENGINE = InnoDB</p>
  
  <p>DEFAULT CHARACTER SET = latin1;</p>