[SOLVED] A Little Help Please?

[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

[eluser]Jan_1[/eluser]
did you try out 'where' earlier then 'join'?

and do i understand this: qu_id = qual_id = NULL ?

[eluser]defectivereject[/eluser]
Yeah i changed the query around to as follows

Code:

function test2($horseyman) {

            $data = array();
            $this->db->where('s_id', $horseyman);
            $this->db->where('qual_id', NULL);
                $this->db->select('Staff.s_id, Staff.r_id, Required_roles.qu_id,Required_roles.role_id, Required_roles.required_by, records.qual_id, qualification.q_id, qualification.qualification');
                $this->db->from('required_roles');
                $this->db->join('records', 'records.qual_id = required_roles.qu_id ', 'left');
                $this->db->join('staff', 'required_roles.role_id = staff.r_id', 'left');
                $this->db->join('qualification', 'required_roles.qu_id = qualification.q_id', 'left ');
                 $Q = $this->db->get();
                    if ($Q->num_rows() > 0){
                       foreach ($Q->result_array() as $row){
                         $data[] = $row;
                       }
        }
                    $Q->free_result();    
                    return $data;        


        }

gives the same results

Code:

[17] => Array
        (
            [s_id] => 96
            [r_id] => 9
            [qu_id] => 116
            [role_id] => 9
            [required_by] => Company Name
            [qual_id] =>
            [q_id] => 116
            [qualification] => Qualification Name
        )

the Qual_id is Null as i'm looking for those.

BUT as with this one r_id is 9 and he needs qu_id of 37.
BUT
s_id of 1 with a role_id of 4 has a qu_id in the records table of 37 so its omityting that, although i'm asking it not to as i'm looking for a qu_id of 37 where r_id is 9


EDIT:
Could it be that i'm not actually specifying where the r_id is 9?
Maybe that is it?

[eluser]defectivereject[/eluser]
i solved it, just needed some additional where statements

Code:

$this->db->select('s_id, r_id, qu_id, staff_id, qual_id, qualification, required_by' );
            $this->db->from('required_roles');
            $this->db->join('staff', 'required_roles.role_id = staff.r_id', 'INNER');
            $this->db->join('records', 'required_roles.qu_id = records.qual_id', 'left outer');
            $this->db->join('qualification', 'qu_id = q_id', 'left outer');
            $this->db->where('s_id', $this->uri->segment(3));
            $this->db->where('staff_id <>', $this->uri->segment(3));
            $this->db->where('staff_id IS NOT NULL');
            $this->db->or_where('s_id', $this->uri->segment(3));
            $this->db->where('staff_id', NULL );
            $this->db->order_by('qu_id', 'DSC');

thanks for the help anyways!