CodeIgniter Forums

Full Version: Multiple join question
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[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

El Forum

[eluser]bubbafoley[/eluser]
your select is wrong

Code:
$this->db->select('p.*, SUM(r.product_price) as revenue, b.*');

El Forum

[eluser]webnology[/eluser]
Oh dear, I should have noticed that :-) But thank you Bubbafoley for the quick response. Appreciate it!