title: Codeigniter Join with Multiple Conditions
body: <p>I'm using Codeigniter Active Records class and I want to join my <code>users</code> table with my <code>clients</code> table, so that I can display the users' "real" names and not just their ID.</p>
  
  <p>Here is what the <code>clients</code> table looks like (example) and the columns a_1, a_2, and a_3 correspond to my <code>users</code> table, specifically, the <code>user id</code>:</p>
  
  <pre><code>  clients
  |--------|---------|----------|-----------|----------|
  |  id    |   name  |   a_1    |    a_2    |   a_3    |
  |--------|---------|----------|-----------|----------|
  |   1    |   Paul  |    2     |     4     |    1     |
  </code></pre>
  
  <p>And my <code>users</code> table looks like this:</p>
  
  <pre><code>   users
  |--------|---------|----------|
  |  id    |  first  |   last   |  
  |--------|---------|----------|
  |   1    |   Paul  |  Blake   |
  |--------|---------|----------|
  |   2    |   Dan   |   Doe    |
  |--------|---------|----------|
  |   3    |  Steve  |  Smith   |
  |--------|---------|----------|
  |   4    |  Harry  |  Jones   |
  |--------|---------|----------|
  </code></pre>
  
  <p>So, essentially, if I were select from the <code>clients</code> table and JOIN it, it would look like this: </p>
  
  <pre><code>  clients
  |--------|---------|----------|-----------|----------|
  |  id    |   name  |   a_1    |    a_2    |   a_3    |
  |--------|---------|----------|-----------|----------|
  |   1    |   Paul  | Dane Doe |Harry Jones|Paul Blake|
  </code></pre>
  
  <p>Thus far, I have tried (which hasn't worked, it just displays the same name for all):</p>
  
  <pre><code>&lt;?
   $this-&gt;db
    -&gt;select('name, a_1, a_2, a_3')
    -&gt;from('clients')
    -&gt;join('users', 'users.id=a_1 OR users.id=a_2 OR users.id=a_3');
  </code></pre>
  
  <p>Any help would be great!</p>
  
  <p><strong>Solution:</strong></p>
  
  <p>Here is what I was able to come up with which works (thanks to @elavarasan lee):</p>
  
  <pre><code>&lt;?
   $this-&gt;db
    -&gt;select('CONCAT(u1.first," ", u1.last) as a_1_name, CONCAT(u2.first," ", u2.last) as a_2_name, CONCAT(u3.first," ",u3.last) as a_3_name', FALSE)
    -&gt;from('clients')
    -&gt;join('users AS u1', 'u1.id=a_1', 'left')
    -&gt;join('users AS u2', 'u2.id=a_2', 'left')
    -&gt;join('users AS u3', 'u3.id=a_3', 'left');
  </code></pre>