Joining tables together in a model function

[eluser]jpidgeon[/eluser]
I am trying to pull together info from 3 tables using the active record class and return this result to my controller.

I am using 3 tables: criteria, tasks and data

I feed in a "task_id" to the crtieria table, and a "qms_id" to the data table.

Both the data and criteria tables are joined via "criteria_id"

A "task_name" is also joined in via the tasks and criteria tables.

The issue I have, is that on every row there is data present on the criteria and tasks tables, but not the data table.

Data is only inserted to the data table when I update using the form, but I still need the controller to feed out all the info, even if some of the fields are coming back blank or not existant.

This is because "qms_id" doesn't exist on the data table every time, therefore the model is returning any results.

When I change the 2nd where clause to or_where it works correctly, but doesn't relat correctly to each task.

This seems quite complicated, but if anyone has any ideas how I can still pull back everything, everytime this it would be much appreciated?

MODEL:

Code:

function include_criteria($task_id, $qms_id)
    {
        $this->db->where('quality_criteria.task_id', $task_id);
        $this->db->where('quality_data.qms_id', $qms_id);
        $this->db->join('quality_tasks', 'quality_tasks.task_id = quality_criteria.task_id');
        $this->db->join('quality_data', 'quality_data.criteria_id = quality_criteria.criteria_id', 'right');
        $this->db->order_by('quality_criteria.criteria_order', "ASC");
        
        $query = $this->db->get('quality_criteria');
        $return['rows'] = $query->result();
        
        $this->db->where('task_id', $this->uri->segment(3, 0));
        $query = $this->db->get('quality_criteria');
        $return['num_rows'] = $query->num_rows();
        
        return $return;
    }