Multiple join question

#1
[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


Messages In This Thread
Multiple join question - by El Forum - 07-18-2011, 09:42 AM
Multiple join question - by El Forum - 07-18-2011, 02:33 PM
Multiple join question - by El Forum - 07-19-2011, 12:00 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.