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

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

我有一个SQL查询，因为我使用了pm1ticket.customer_id.但它在"on子句"中显示"未知"列"pm1ticket.customer_id"。

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

Answer 1

请尝试使用以下代码

JOIN pm1ticket ON pm1ticket.id = pm1flag_history.ticketID            
JOIN pm1contact ON 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;
}