[eluser]webnology[/eluser]
Hi all,
I have 3 tables, products, revenue and brands. When showing the list of products, I want to show the sum of all prices (revenue table), the name of the brand (linked to the product from the brands table), and some other data from the products table.
This is my working code, excluding ther name of the brand, which works fine.
Code:
$this->db->select('p.*, SUM(r.product_price) as revenue');
$this->db->from('products as p');
$this->db->join('revenue as r', 'r.product_id = p.product_id', 'left');
$this->db->order_by('p.product', 'asc');
$this->db->group_by('p.product_id');
$this->db->where('p.user_id', $user_id, FALSE);
This is my trial for adding the brand name, and I can't make it work.
Code:
$this->db->select('p.*, SUM(r.product_price) as revenue', 'b.*');
$this->db->from('products as p');
$this->db->join('revenue as r', 'r.product_id = p.product_id', 'left');
$this->db->join('brands as b', 'b.brand_id = p.brand_id','left');
$this->db->order_by('p.product', 'asc');
$this->db->group_by('p.product_id');
$this->db->where('p.user_id', $user_id, FALSE);
Can someone guide me to the solution here? All help is really appreciated.
Regards,
Michel