Multiple Tables in FROM Statement in DB Active Record

Multiple Tables in FROM Statement in DB Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Multiple Tables in FROM Statement in DB Active Record (/showthread.php?tid=7530)

Multiple Tables in FROM Statement in DB Active Record - El Forum - 04-13-2008

[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

Multiple Tables in FROM Statement in DB Active Record - El Forum - 04-13-2008

[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'));