title: MySQL: alternate to left join over three tables
body: <p>I have three tables with the following structure.</p>
  <blockquote>
  <p><strong>Table child</strong></p>
  <p>ID , Name, ....</p>
  <p>3,  John, ...</p>
  <p>5, erik,....</p>
  <p>7, Maya</p>
  </blockquote>
  <p>The child id is related to teacher in the 2nd table.</p>
  <blockquote>
  <p>Table teacher_child_relation</p>
  <p>id, child_id, teacher_id</p>
  <p>1, 3(john), 4(roberts)</p>
  <p>2, 5(erik), 9(maria)</p>
  </blockquote>
  <p>The teachers details are in the third table.</p>
  <blockquote>
  <p>Table teacher</p>
  <p>id, Name,....</p>
  <p>4, Roberts</p>
  <p>9, Maria</p>
  </blockquote>
  <p>Now I am running the following query to get the Named of teacher assigned to a child. I am using the following query.</p>
  <pre><code>SELECT c.*, t.name,
  FROM child c, teacher_child_relation ct, teacher t
  WHERE c.id = ct.child_id AND ct.teacher_id = t.id
  </code></pre>
  <p>This returns me only those records of children who are assigned a teacher. I want a query that returns all the records of child and show an empty field where teacher is not assigned.</p>