title: MySQL join query on three tables which connected to each other
body: <p>I have three tables like this:</p>
  
  <p><strong>Animal</strong></p>
  
  <pre><code>| id | cat_id | horse_id | dog_id |
  |:--:|:------:|:--------:|--------|
  |  1 |   15   |    16    | 17     |
  </code></pre>
  
  <p><strong>Cat</strong></p>
  
  <pre><code>| id | lang_id |
  |:--:|:-------:|
  | 15 |   3716  | 
  </code></pre>
  
  <p><strong>Horse</strong></p>
  
  <pre><code>| id | lang_id |
  |:--:|:-------:|
  | 16 |   3717  | 
  </code></pre>
  
  <p><strong>Dog</strong></p>
  
  <pre><code>| id | lang_id |
  |:--:|:-------:|
  | 17 |   3718  | 
  </code></pre>
  
  <p><strong>Language</strong></p>
  
  <pre><code>|  id  |      en        |
  |:----:|:--------------:|
  | 3716 | BRAVE LEGEND   |
  | 3717 | N Rawiller     |
  | 3718 | DRAGON GENERAL |
  </code></pre>
  
  <p>I want to get all the animals <code>en</code> name from <code>Language</code> table through each animal's table <code>lang_id</code>. It would be like by using <code>Animal</code>: <code>cat_id</code> -> <code>Cat</code>: <code>lang_id</code> -> <code>Language</code>: <code>en</code>. The final results would be like:</p>
  
  <pre><code>| animal_id |   cat_name   | horse_name |    dog_name    |
  |:---------:|:------------:|:----------:|:--------------:|
  |     1     | BRAVE LEGEND | N Rawiller | DRAGON GENERAL |
  </code></pre>
  
  <p>I am trying to use this:</p>
  
  <pre><code>select animal.id, lang.* from animal 
  left join cat on animal.cat_id = cat.id
  left join horse on animal.horse_id = horse.id
  left join dog on animal.dog_id = dog.id
  left join lang on cat.lang_id = lang.id or (horse.lang_id = lang.id) or (dog.lang_id = lang.id)
  where animal.id = 1
  </code></pre>
  
  <p>But I can't get <code>cat_name</code>, <code>dog_name</code>, <code>horse_name</code> because it's all coming from the same <code>Language</code> table.</p>