Codeigniter Join with Multiple Conditions

codeigniter, join, mysql

Solution

@Dodinas: I got the solution interms of MYSQL Query. I am finding it hard to convert the query into CI Active Record. But try this:

    $sql = "SELECT `name`, CONCAT(`u1`.`first`,' ', `u1`.`last`) as a_1, 
                           CONCAT(`u2`.`first`,' ', `u2`.`last`) as a_2, 
                           CONCAT(`u3`.`first`,' ', `u3`.`last`) as a_3 
            FROM `clients` 
            LEFT JOIN `users` as `u1` ON (`u1`.`id`=`a_1`)
            LEFT JOIN `users` as `u2` ON (`u2`.`id`=`a_2`)
            LEFT JOIN `users` as `u3` ON (`u3`.`id`=`a_3`)";

    $result = $this->db->query($sql);

Problem

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

Original source