Return of Equal Employee

Question

I have the following code

$sql = "
        SELECT 
            c.*, 
            cl.razaosocial, 
            cl.idCliente 
        FROM 
            chamada as c, 
            cliente as cl 
        WHERE 
            c.idCliente = cl.idCliente";
$consulta = $this->db->query($sql)->result();

foreach($consulta as &$valor)
{
    $this->db->where('idFuncionario', $valor->idFuncionario);
    if($this->db->get('funcionario')->row('nome'))
    {
        $valor->funcionarioNome = $this->db->get('funcionario')->row('nome');
    }
    else 
    {
        $valor->funcionarioNome = "";   
    }
}

return $consulta;

He returns in the $valor->funcionarioNome always the same name.

What would have been wrong there?

I thought to try to make this SQL, it only displays those who are set the employees in the call, but there are calls in "open" no elected officials yet.

SELECT c.*, f.nome as nomeFuncionario, f.idFuncionario, cl.razaosocial, cl.idCliente FROM chamada as c, cliente as cl, funcionario as f WHERE c.idCliente = cl.idCliente AND c.idFuncionario = f.idFuncionario GROUP BY c.idFuncionario

Answer 1

If you want to add the employee name on the return of the query is not easier to get it on SELECT?

I believe that your idFuncionario is on the table Chamada, then:

$sql = "SELECT DISTINCT c.*, 
               cl.razaosocial, 
               cl.idCliente,
               f.nome as funcionarioNome
            FROM chamada as c
            INNER JOIN cliente as cl on c.idCliente = cl.idCliente
            LEFT JOIN funcionario as f on c.idFuncionario = f.idFuncionario ";

Placed LEFT JOIN for it seems that not always you have the employee.