CodeIgniter Forums
selecting from multiple tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: selecting from multiple tables (/thread-18983.html)



selecting from multiple tables - El Forum - 05-24-2009

[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


selecting from multiple tables - El Forum - 05-24-2009

[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.


selecting from multiple tables - El Forum - 05-24-2009

[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 Smile