Join and join and join...

[eluser]webnology[/eluser]
Hi all,

I have this (working) code:

Code:

$this->db->select('*, arrangements.price as arr_price, treatments.price as tr_price');
$this->db->from('remarks');
$this->db->join('treatments', 'treatments.treatment_id = remarks.treatment_id','left');
$this->db->join('arrangements', 'arrangements.arrangement_id = remarks.arrangement_id','left');
$this->db->join('products', 'products.product_id = remarks.product_id','left');
$this->db->order_by('date', 'desc');
$this->db->where('customer_id', $customer_id, FALSE);

In my remarks table, I had up to now only 1 product_id per row. Now we need to add 2 more product_id's in the same db row. So, we add product_id2 and product_id3. I need to get the product description from the products table via a JOIN.

I can't get my code to work with these extra items. It should be sth like this, I suppose:

Code:

$this->db->select('*, arrangements.price as arr_price, treatments.price as tr_price');
$this->db->from('remarks');
$this->db->join('treatments', 'treatments.treatment_id = remarks.treatment_id','left');
$this->db->join('arrangements', 'arrangements.arrangement_id = remarks.arrangement_id','left');
$this->db->join('products', 'products.product_id = remarks.product_id','left');
$this->db->join('products', 'products.product_id = remarks.product_id2','left');
$this->db->join('products', 'products.product_id = remarks.product_id3','left');
$this->db->order_by('date', 'desc');
$this->db->where('customer_id', $customer_id, FALSE);

Which doesn't work, I know. But anyone any idea how to get this working?

All help much appreciated,
Michel