Need to simplify complicated db query

[eluser]rvillalon[/eluser]
I have multiple products and each product may have multiple images associated with it. Additionally, each image has a sort # associated with it too. For example, I may have a product called issue 001 and it has 3 images. I have a sort table where I can tell my controller which to show first. Other times, products won't have images associated with it therefore also won't have a sort #s.

I'm trying to come up with an Active Record query but I'm having issues combining all of it into one multidimensional array. Basically, what I want to be able to do is run a foreach loop on the product and check if there are any images associated with it. If there is, then I display each one.

products
----------------------------------------------------------------
| id - product_id (alternative id) | owner_id | name |
----------------------------------------------------------------
| 1 - ea89b3d81db5373983d5b6ded22188ed - 300 | issue 001 |
----------------------------------------------------------------
| 2 - 40aa6b10b58e6643fb3797f5f895f1aa - 300 | issue 002 |
----------------------------------------------------------------

images
----------------------------------------------------------
| id | product_id (same as above) | file_name |
----------------------------------------------------------
| 1 - ea89b3d81db5373983d5b6ded22188ed - imagename1.jpg |
----------------------------------------------------------
| 2 | ea89b3d81db5373983d5b6ded22188ed | imagename2.jpg |
----------------------------------------------------------
| 3 | ea89b3d81db5373983d5b6ded22188ed | imagename3.jpg |
----------------------------------------------------------

image_sort
---------------------------------------------------------------------------------------
| id | product_id (same as above) | image_id (same as above) | listing (sort #) |
---------------------------------------------------------------------------------------
| 1 | ea89b3d81db5373983d5b6ded22188ed | 2 | 1 |
---------------------------------------------------------------------------------------
| 2 | ea89b3d81db5373983d5b6ded22188ed | 1 | 2 |
---------------------------------------------------------------------------------------
| 3 | ea89b3d81db5373983d5b6ded22188ed | 3 | 3 |
---------------------------------------------------------------------------------------

I tried doing the JOIN statement but with no luck.

Code:

$this->db->select('*');
$this->db->from(self::TABLE_PRODUCTS);
$this->db->join(self::TABLE_IMAGES, self::TABLE_IMAGES . '.product_id = ' . self::TABLE_PRODUCTS . '.product_id');
$this->db->join(self::TABLE_SORT, self::TABLE_SORT . '.image_id = ' . self::TABLE_IMAGES . '.id');
$this->db->where(self::TABLE_PRODUCTS . '.owner_id', $id);
$query = $this->db->get();

if ($query->num_rows() > 0) return $query->result_array();
return array();

[eluser]davidbehler[/eluser]
What exactly is your problem?
Products without images don't appear in your result?

Try using using left outer join:

Code:

$this->db->select('*');
$this->db->from(self::TABLE_PRODUCTS);
$this->db->join(self::TABLE_IMAGES, self::TABLE_IMAGES . '.product_id = ' . self::TABLE_PRODUCTS . '.product_id', 'left outer');
$this->db->join(self::TABLE_SORT, self::TABLE_SORT . '.image_id = ' . self::TABLE_IMAGES . '.id', 'left outer');
$this->db->where(self::TABLE_PRODUCTS . '.owner_id', $id);
$query = $this->db->get();

if ($query->num_rows() > 0) return $query->result_array();
return array();

[eluser]rvillalon[/eluser]
Actually, the 'left outer' fixed my problem! Thanks waldmeister!