+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: Multiple join question (/thread-43612.html)
Multiple join question - El Forum - 07-18-2011
[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
Multiple join question - El Forum - 07-18-2011
[eluser]bubbafoley[/eluser]
your select is wrong
Code:
$this->db->select('p.*, SUM(r.product_price) as revenue, b.*');Multiple join question - El Forum - 07-19-2011
[eluser]webnology[/eluser]
Oh dear, I should have noticed that :-) But thank you Bubbafoley for the quick response. Appreciate it!