[eluser]boldyellow[/eluser]
I've found active records for queries a huge help... especially for joins.
I'm still learning CI, but the documentation for active records makes it easy to understand the example queries in there.
Specifically, for my current project, I had two tables (gallery_names, gallery_types) to join for the gallery name (like, Fall 2010) and the gallery type (like, Student Photos).
Code:
$this->db->select('gallery_names.*, gallery_types.type_name');
$this->db->from('gallery_names');
$this->db->where('gallery_names.id', $id);
$this->db->join('gallery_types', 'gallery_types.id = gallery_names.gallery_type');
$query = $this->db->get();