title: How do you do a condition SQL left join based on the status of the second table?
body: <p>So for example I have two tables</p>
  
  <pre><code>Person Table
  ID : Name
  1 : Bob
  2 : Jane
  3 : Mike
  4 : John
  5: Mary
  </code></pre>
  
  <p>I then also have (current is actually an integer but I used text to make it more readable):</p>
  
  <pre><code>Job Table
  ID : Name : PersonID : JobStatus
  1 : Plumber : 1 : current
  2 : Doctor : 2 : past
  3 : Driver : 2 : current
  4 : Cook : 3 : current
  5: Programmer : 5 : past
  </code></pre>
  
  <p>I would like to join the two tables so that I can get a list of everyone, but I only want to include current jobs with the left join. In other words I want the result to be:</p>
  
  <pre><code>Bob : Plumber : current
  Jane : Driver : current
  Mike : Cook : current
  John : :  
  Mary : : 
  </code></pre>
  
  <p>How can I do this with a join. I tried doing:</p>
  
  <pre><code>SELECT 
    person.name, job.name, job.status 
  FROM 
    person
  LEFT JOIN 
    job ON person.id=job.person.id
  </code></pre>
  
  <p>But the problem is that in this case I will see two entries for Jane, one for her past job of Doctor as well as her new job of Driver. And if I add a where clause such as:</p>
  
  <pre><code>WHERE job.status='current' 
  </code></pre>
  
  <p>Then it removes removes John and Mary from the list.</p>
  
  <p>How can I write a join that will show all the people yet only include jobs that are current? </p>