Multiple Tables in FROM Statement in DB Active Record

[eluser]lay-z-cow[/eluser]
Hi,

maybe this question is stupid, but I couldn't find anything about this in the User Guide:

I want to start a mySQL query like this:

Code:

SELECT
person.person_id,
person.name,
bew2proj.status_id,
bew2proj.b2w_id,
company.company_id,
company.name
FROM
bew2proj,
person,
company
WHERE
bew2proj.person_id = person.person_id
AND person.company_id = company_id.company_id_id
AND person.company_id = ' . $company_id . '
AND bew2proj.projekt_id = ' . $_POST['ID'];

How do I rewrite this query for the Active Record? Do I have to use JOIN?


greetings

Chris

[eluser]Seppo[/eluser]
You can do it with active record... however it's better if you use Joins instead...
As you posted it

Code:

$this->db->select('person.person_id, person.name, bew2proj.status_id, bew2proj.b2w_id, company.company_id, company.name');
        $this->db->from(array('bew2proj', 'person', 'company'));
        $this->db->where('bew2proj.person_id = person.person_id');
        $this->db->where('person.company_id = company_id.company_id_id');
        $this->db->where('person.company_id', $company_id);
        $this->db->where('bew2proj.projekt_id', $this->input->post('ID'));

Using joins

Code:

$this->db->select('person.person_id, person.name, bew2proj.status_id, bew2proj.b2w_id, company.company_id, company.name');
        $this->db->from('bew2proj');
        $this->db->join('person', 'bew2proj.person_id = person.person_id');
        $this->db->join('company', 'person.company_id = company_id.company_id_id');
        $this->db->where('person.company_id', $company_id);
        $this->db->where('bew2proj.projekt_id', $this->input->post('ID'));