Unknown column 'pm1ticket.customer_id' in 'on clause

Question

public function flag_history()
    { 
        $res=$this->db->query("SELECT
                pm1ticket.rep_id AS execut,
                pm1ticket.status_id,
                pm1contact_rep.f_name AS fname,
                pm1contact_rep.l_name AS lname,
                pm1ticket.due_date,
                pm1flag_history.id,
                flagid,
                from_id,
                to_id,
                MAX.time,
                ticketID,
                pm1flag_history.rep_id,
                pm1contact_rep.user_name,
                pm1contact_rep.f_name,
                pm1contact_rep.l_name,
                pm1ticket.subject
            FROM
                pm1flag_history
            JOIN pm1contact_rep ON pm1contact_rep.id = pm1flag_history.to_id
            JOIN pm1contact ON pm1contact.id = pm1ticket.customer_id
            JOIN pm1ticket ON pm1ticket.id = pm1flag_history.ticketID
            INNER JOIN(
                SELECT
                    MAX(TIME) AS TIME,
                    ticketID AS tkt
                FROM
                    pm1flag_history
                WHERE
                    from_id = "
                .$this->SESSION->userdata('TechUser_Id')."
                GROUP BY
                    tkt
            ) MAX
            ON
                MAX.tkt = pm1flag_history.ticketID
            WHERE
                from_id = ".$this->SESSION->userdata('TechUser_Id')." AND flagid = 'rep'
            GROUP BY
                ticketID
            ORDER BY
                TIME
            DESC"
        );
        //echo $this->db->last_query();die;
        $result=$res->result_array();
        return $result;
    }

i have a SQL query in that i have used pm1ticket.customer_id. but it is showing the Unknown column 'pm1ticket.customer_id' in 'on clause.

How to resolve this error. can anyone please help me.

Answer 1

Please try with below code

JOIN pm1ticket ON pm1ticket.id = pm1flag_history.ticketID            
JOIN pm1contact ON pm1contact.id = pm1ticket.customer_id   

these two lines order was wrong.

public function flag_history()
    { 
        $res=$this->db->query("SELECT
                pm1ticket.rep_id AS execut,
                pm1ticket.status_id,
                pm1contact_rep.f_name AS fname,
                pm1contact_rep.l_name AS lname,
                pm1ticket.due_date,
                pm1flag_history.id,
                flagid,
                from_id,
                to_id,
                MAX.time,
                ticketID,
                pm1flag_history.rep_id,
                pm1contact_rep.user_name,
                pm1contact_rep.f_name,
                pm1contact_rep.l_name,
                pm1ticket.subject
            FROM
                pm1flag_history
            JOIN pm1contact_rep ON pm1contact_rep.id = pm1flag_history.to_id
            JOIN pm1ticket ON pm1ticket.id = pm1flag_history.ticketID
            JOIN pm1contact ON pm1contact.id = pm1ticket.customer_id            
            INNER JOIN(
                SELECT
                    MAX(TIME) AS TIME,
                    ticketID AS tkt
                FROM
                    pm1flag_history
                WHERE
                    from_id = "
                .$this->SESSION->userdata('TechUser_Id')."
                GROUP BY
                    tkt
            ) MAX
            ON
                MAX.tkt = pm1flag_history.ticketID
            WHERE
                from_id = ".$this->SESSION->userdata('TechUser_Id')." AND flagid = 'rep'
            GROUP BY
                ticketID
            ORDER BY
                TIME
            DESC"
        );
        //echo $this->db->last_query();die;
        $result=$res->result_array();
        return $result;
    }