MySQL question

[eluser]codex[/eluser]
Imagine 2 tables: users and projects

In the projects table 'developer' and 'contractor' are stored by id. Now I need to get both names with one query, but I don't know if it's possible to do it.

I have this, but this only gets 1 name. Adding another join doesn't work. Can you even get 2 names in one query?

Code:

function get_project_details($project_id)
    {
        $this->db->select('projects.*, users.user_name');
        $this->db->from('projects');
        $this->db->join('users', 'user_id = project_contractor_id', 'left');
        $this->db->where('project_id', $project_id);
        $query = $this->db->get();

        return $query->result();
    }

[eluser]tonanbarbarian[/eluser]
try this, you join the user table twice but give it an alias each time

Code:

function get_project_details($project_id)
    {
        $this->db->select('projects.*, developers.user_name, contractors.user_name');
        $this->db->from('projects');
        $this->db->join('users AS developers', 'developers.user_id = project_developer_id', 'left');
        $this->db->join('users AS contractors', 'contractors.user_id = project_contractor_id', 'left');
        $this->db->where('project_id', $project_id);
        $query = $this->db->get();

        return $query->result();
    }

If that does not work you may have to write the query manually rather than with the active record functions

Code:

SELECT projects.*, developers.user_name, contractors.user_name
FROM projects
LEFT JOIN users AS developers ON (developers.user_id = projects.project_developer_id)
LEFT JOIN users AS contractors ON (contractors.user_id = prjects.project_contractor_id)
WHERE projects.project_id = ?

[eluser]codex[/eluser]
[quote author="tonanbarbarian" date="1199072711"]try this, you join the user table twice but give it an alias each time

Code:

function get_project_details($project_id)
    {
        $this->db->select('projects.*, developers.user_name, contractors.user_name');
        $this->db->from('projects');
        $this->db->join('users AS developers', 'developers.user_id = project_developer_id', 'left');
        $this->db->join('users AS contractors', 'contractors.user_id = project_contractor_id', 'left');
        $this->db->where('project_id', $project_id);
        $query = $this->db->get();

        return $query->result();
    }

If that does not work you may have to write the query manually rather than with the active record functions

Code:

SELECT projects.*, developers.user_name, contractors.user_name
FROM projects
LEFT JOIN users AS developers ON (developers.user_id = projects.project_developer_id)
LEFT JOIN users AS contractors ON (contractors.user_id = prjects.project_contractor_id)
WHERE projects.project_id = ?

[/quote]

Wow, thanks! I tried aliasing the fields, but I now realize I had to alias the table as well. You just made my day