Sql insert,select using CodeIgniter

Question

I have a problem and do not know how to solve, please help me, So I have 2 tables in my database:

dosar
id  name  date  fk_verificator  fk_administrator  fk_user
user
id name surname is_verificator  is_admin

I create and insert into my db, in fk_verificator is stored the id from user who is verificator and in fk_administrator is sored the id from user who is administratr, So my question is it's possible to create a query to get for this dosar 2 users from user table(one user who is administrator and one who is admin) or need to create an auxiliary table? thnx in advance.Please help me So for example if I have:

dosar table:
id   name       fk_verif   fk_admin   fk_user
1   dosarNr.1     2          1           1

user
id     name     surname   is_verific   is_admin
 1     name1    surname1     N           Y
 2     name2    surname2     Y           N

Now in this select I want to get:

dosar1    name1   surname1   name2   surname2

Answer 1

For your expected result set you have to join your user table twice with doser in order to get the name for admin and the person who verifies records

Mysql Query

select 
d.name,
u1.name name1,
u1.surname surname1,
u1.name name2,
u1.surname surname2
from dosar d
join `user` u1 on(d.fk_admin = u1.id and u1.is_admin = 'Y')
join `user` u2 on(d.fk_verif = u2.id and u2.is_verific = 'Y')

FIDDLE DEMO

Active Record Query

$result=$this->db->select('d.name,
                   u1.name name1,
                   u1.surname surname1,
                   u1.name name2,
                   u1.surname surname2')
          ->from('dosar d')
          ->join("user u1','d.fk_admin = u1.id and u1.is_admin = 'Y'")
          ->join("user u2','d.fk_verif = u2.id and u2.is_verific = 'Y'")
          ->get()
          ->result();

Running simple Query

$sql="select 
    d.name,
    u1.name name1,
    u1.surname surname1,
    u1.name name2,
    u1.surname surname2
    from dosar d
    join `user` u1 on(d.fk_admin = u1.id and u1.is_admin = 'Y')
    join `user` u2 on(d.fk_verif = u2.id and u2.is_verific = 'Y')";
$this->db->query($sql);