selecting from multiple tables

[eluser]blu3ness[/eluser]
I'm trying to do an old fashion select from two tables

in plain SQL it'll prob be something like this

Code:

SELECT a.title, a.desc,b.title as cat_title,b.desc as cat_desc
FROM products a, categories b
WHERE a.category_id = b.id

How would you accomplish this using activerecord?

I tried

Code:

$this->db->select('*')->from('products')->where('category_id',$category_id)->join('categories','products.category_id=categories.id');

which only gets me the first table data and nothing from the second.

Tyvm

[eluser]TheFuzzy0ne[/eluser]
I wonder if this will work any better:

Code:

$this->db->select('a.title, a.desc, b.title AS cat_title, b.desc AS cat_desc');
         ->from('products a');
         ->join('categories b', 'products.category_id=categories.id', 'INNER');
         ->where('a.category_id', 'b.id');

$res = $this->db->get();

I'm not entirely sure about this but you might need to move the call to $this->db->select() to under the from and join calls. I seem to recall that CodeIgniter had a problem with tracking alias', but I am not certain that I recall correctly.

[eluser]blu3ness[/eluser]

Code:

$this->db->select('a.*, b.title AS cat_title')
->from('products a')
->join('categories b','a.category_id = b.id','INNER')
->where('category_id',$category_id);

worked

Thanks TheFuzzyOne