[eluser]defectivereject[/eluser]
Hi all,
I'm trying to code a skills matrix as a project to make life easier at work.
I've done the large bulk of it, but have hit one problem.
Now i have 8 tables, and in one query i'm using 4 of them to check for qualifications that an individual should have in their role.
Now i have a
'Required_roles' table containing a role_id, a qu_id
'qualification' table containing q_id, qualification (q_id matches qu_id)
'records' table containing staff_id and the qual_id (qual_id matches q_id and qu_id)
'staff' table containing r_id, s_id (r_id matches role_id and the s_id matches staff_id)
there are other fields in these tables but i don't need those fields.
Now i run a query from my controller that gets the qualifications held by an individual and that runs no problems.
i then run a second query to get those that the individual should have, but doesn't. which runs and returns the ones they don't currently hold BUT if someone of a different role holds that qualification it omits that one, but i don't want it to!!!
Here is my query, so if anyone can help i would be grateful
as for what $horseyman is.... thats just passing on the uri/segment which is the persons s_id
Code:
function test2($horseyman) {
$data = array();
$this->db->select('*');
$this->db->from('required_roles');
$this->db->join('records', 'qu_id = qual_id', 'left outer');
$this->db->join('staff', 'r_id = role_id', 'inner');
$this->db->join('qualification', 'q_id = qu_id', 'left outer');
$this->db->where('qual_id', NULL);
$this->db->where('s_id',$horseyman);
$Q = $this->db->get();
if ($Q->num_rows() > 0){
foreach ($Q->result_array() as $row){
$data[] = $row;
}
}
$Q->free_result();
return $data;
Many Thanks in advance