时间戳

首页 > 解决方案 > 'on 子句中的未知列'pm1ticket.customer_id'

javascript - 'on 子句中的未知列'pm1ticket.customer_id'

问题描述

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;
    }

我有一个我用过的 SQL 查询pm1ticket.customer_id。但它在'on 子句中显示未知列'pm1ticket.customer_id'。

如何解决此错误。谁能帮帮我吗。

标签： javascriptphpsqlcodeigniter

解决方案

请尝试以下代码

加入 pm1ticket 上 pm1ticket.id = pm1flag_history.ticketID
加入 pm1contact 上 pm1contact.id = pm1ticket.customer_id

这两行的顺序是错误的。

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;
    }

如果此答案有帮助，请接受此答案，以便对其他人有所帮助

推荐阅读