[SOLVED] Using the results from one SQL statement in another

[eluser]theshiftexchange[/eluser]
Hey all,

How do I use the results from one SQL search in another?

Basically below are two functions which work correctly at the moment (independently of each other).

However what I want is for the "get_overtime" function to include the total amount of overtime worked so far (the result of the second function).

I'm not sure how to make a single SQL statement which does all of that?

Code:

function get_overtime($date)
{
            $this->db->where ('date',$date);  
            $this->db->join ('users', 'users.user_ID = overtime_list.user_ID');
            $this->db->join ('stations', 'users.station_ID = stations.station_ID');
            $query = $this->db->get('overtime_list');
            return $query->result_array();
}

Code:

function get_user_overtime_total($user_ID)
{
            $this->db->select_sum ('shift_length');
            $this->db->where ('user_ID',$user_ID);
            $query = $this->db->get('overtime_assigned');
            return $query->row()->shift_length;
}

Any help would be appreciated

[eluser]Krzemo[/eluser]
Ey,

Subqueries is you keyword

1) it can be easly done writing regular query (not using CI AR)
2) http://shawnmccool.com/2009/09/18/using-...ubqueries/

Have fun!
Regs
K

[eluser]theshiftexchange[/eluser]
perfect - thanks so much