Codeigniter: Joining multiple tables

I have 3 tables with the following columns

ITEMS
-id
-name
-category_id
-brand_id

ITEM_BRAND
-id
-name

ITEM_CATEGORY
-id
-name

I joined them by

$this->db->order_by('items.name', 'ASC');
    $this->db->join('item_brand', 'item_brand.id = items.brand_id');
    $this->db->join('item_category', 'item_category.id = items.category_id');
    $query = $this->db->get("items");

Question:

Since all columns are selected, is it possible to select a column from a definite table(such as item_brand.name)?

I did the following code, yet I am getting zero results. but if I remove this following line,

$this->db->join('item_category', 'item_category.id = items.category_id');

and loading $data['name'] to the view, all I get is the brand.name

I need to join the 3 tables. But I failed to do so.

Edit: I managed to make a solution for the columns, however, I’m getting zero results joining the 3 tables.

$this->db->select('items.name, 
        items.category_id, 
        items.srp, 
        items.dp, 
        items.brand_id, 
        items.id, 
        items.serial, 
        item_brand.name AS item_brand, 
        item_category.name AS item_category');
    $this->db->order_by('items.name', 'ASC');        
    $this->db->join('item_category', 'item_category.id = items.category_id');
    $this->db->join('item_brand', 'item_brand.id = items.brand_id');
    $query = $this->db->get("items");

Answer

Is it possible to select a column from a definite table(such as item_brand.name)?

Yes, you can select column from definite table using select as below :

 $this->db->select('item_brand.name');
 $this->from('items');
 $this->db->join('item_brand', 'item_brand.id = ems.brand_id');
 $this->db->join('item_category', 'item_category.id = items.category_id');
  $query = $this->db->get("items");

And if you are not getting results while joining your item_category table i.e.

$this->db->join('item_category', 'item_category.id = items.category_id');

then there must be issue with the foreign key i.e. join uses inner join of mysql which takes all intersect(common) data between the joined tables and there must be no matching id(foreign key) in item_category and items table. So, you can use rightjoin to get the data. Which will take all data from items table and all the data from item_category table if there is any matching foreign key (items_category.id) from items table. So, what you can do is :

 $this->db->select('item_brand.name');
 $this->from('items');
 $this->db->join('item_brand', 'item_brand.id = ems.brand_id');
 $this->db->join('item_category', 'item_category.id = items.category_id','right');
  $query = $this->db->get("items");

If you want to explore more then you can visit this doc .

Hope it helps. Happy coding 🙂 .