Error 1054 in Database

Question

A Database Error Occurred Error Number: 1054

Unknown column 'reference.idReference' in 'on clause' SELECT os., clientes., facas., usuarios.email, usuarios.nome, referencia. FROM os JOIN clientes ON clientes.idClientes = os.clientes_id JOIN usuarios ON usuarios.idUsuarios = os.usuarios_id JOIN facas ON facas.idFacas = os.faca_id JOIN referencia ON referencia.idReferencia = os.referencia_id WHERE os.idOs = '27' LIMIT 1 Filename: models/Os_model.php Line Number: 96

File containing the Function:

function getById($id){
    $this->db->select('os.*, clientes.*, facas.*, usuarios.email, usuarios.nome, referencia.*');
    $this->db->from('os');
    $this->db->join('clientes','clientes.idClientes = os.clientes_id');
    $this->db->join('usuarios','usuarios.idUsuarios = os.usuarios_id');
    $this->db->join('facas','facas.idFacas = os.faca_id');
    $this->db->join('referencia','referencia.idReferencia = os.referencia_id');
    $this->db->where('os.idOs',$id);
    $this->db->limit(1);
    return $this->db->get()->row();
}

The referenced column exists in the BD.

What can it be ?

Answer 1

You could have passed the diagram of the tables...
But I think the problem with your Join is that idReference maybe there is no see if that’s it, try to rotate this Query manually on your SGBD (Database Management System) Remember to change if idReference if it does not exist in the query

Query

        SELECT O.*,C.*,F.*,U.EMAIL,U.NOME,RE.* FROM
            OS O,
            CLIENTES C,
            FACAS F,
            USUARIOS U,
            REFERENCIA RE
        WHERE
        C.idClientes = O.clientes_id AND 
        U.idUsuarios = O.usuarios_id AND
        F.idFacas = O.faca_id AND
        RE.idReferencia = O.rederencia_id AND --veja se idReferencia existe na tabela referencia
        O.idOs = 1 ; --AO INVES DE 1 RODAR ID PRETENDIDO