CI and JOINed SQL queries question

[eluser]Unknown[/eluser]
I have a database with three tables: "product", "category", "subcategory".
Each product has a category and a subcategory field. There is a number in that field, which is the same as the cat_id and subcat_id, taken from the respective other table.
The category and subcategory tables have a field for their ids and for the cat or subcat name (a string). The subcat table also has a column for the category to which a subcategory belongs.

By following tutorials, I've managed display the results of a query from a single table: the names of all nine products from the "product" table, listed as <li> elements. That was not so tough.

My question is -- how do I go about executing a more complicated query, which I will need if I want to display the actual names of the products' categories and subcategories, instead of just the cat_id and subcat_id (these integers are the only cat/subcat-related data stored in the products table).

I will need to execute a query that looks like:

SELECT `product`.`name`, `subcat`.`name`, `cat`.`name` FROM `product`
LEFT JOIN `cat` ON `cat`.`cat_id` = `product`.`cat_id`
LEFT JOIN `subcat` ON `subcat`.`subcat_id` = `product`.`subcat_id`
WHERE `prod_id` = 1

How do I execute a query like this one? From what I read in the tutorial, I only know how to make a query that selects from just one table:

Code:

$query = $this->db->get('product', 10);

[eluser]Unknown[/eluser]
Why dont you run the query using the following code?

Code:

$query = $this->db->query(' your query ');

[eluser]Unknown[/eluser]
It works I just had to add "AS" definitions for the category and subcategory names because all the resulting fields were called "name". Thanks!

[eluser]danmontgomery[/eluser]
or, read the user guide