我正在尝试从"注释"表中获取结果,但'readsbaby'
表中的结果除外。我得到的结果来自评论,但NOT EXISTS
语句没有效果,所以结果是所有评论。
这两个表都有不应包含在结果中的通用数据。我多次检查数据和语法。尽管如此,此查询仍将返回所有注释,而不考虑AND NOT EXISTS
关闭。
public function get_user_comments($post_id)
{
$user_id = $this->session->userdata('id');
$group_id = $this->session->userdata('group_id');
$sql = "SELECT *
FROM comments
WHERE DATE(created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND comments.group_id = " . $group_id . "
AND comments.user_id != " . $user_id . "
AND NOT EXISTS ( SELECT *
FROM readsbaby
WHERE comments.id = readsbaby.notification_id
AND comments.group_id = readsbaby.group_id
AND readsbaby.user_id = " . $this->session->userdata('id') . "
AND comments.nature1 = readsbaby.notification_type
) ";
return $data=$this->db->query($sql)->result_array();
}
期望通过NOT EXISTS
关闭过滤结果。
我会以这种方式更改查询,以仅让表中没有任何匹配项的行读取baby:
public function get_user_comments($post_id)
{
$user_id = $this->session->userdata('id');
$group_id = $this->session->userdata('group_id');
$sql = "SELECT *
FROM comments
LEFT OUTER JOIN readsbaby ON comments.id = readsbaby.notification_id
AND comments.group_id = readsbaby.group_id
AND readsbaby.user_id = " . $this->session->userdata('id') . "
AND comments.nature1 = readsbaby.notification_type
WHERE DATE(created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND comments.group_id = " . $group_id . "
AND comments.user_id != " . $user_id . "
AND ISNULL(readsbaby.notification_id )";
return $data=$this->db->query($sql)->result_array();
}
使用正确的 mysql JOIN
更容易完成此操作。
您的帖子中,我收集到您想从readsbaby
中不存在的comments
中检索所有值。 comments
中的id
列与任何"匹配"(重复)条目的notification_id
相关联readsbaby
。
基于这个假设,您可以在简单的级别上执行此操作,如下所示
SELECT c.* FROM comments c
LEFT JOIN readsbaby r ON c.id = r.id WHERE r.notification_id IS NULL;
。它应该翻译成您的上下文,如下所示:
SELECT c.* FROM comments c
LEFT JOIN readsbaby r ON c.id = r.notification_id
WHERE
r.notification_id IS NULL
AND DATE(c.created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND c.group_id = " . $group_id . "
AND c.user_id != " . $user_id . "
如果我弄错了,并且您只想获取两者中不存在的数据,则需要使用以下内容:
SELECT c.* FROM comments c
LEFT JOIN readsbaby r ON c.id = r.notification_id
WHERE
r.notification_id IS NULL
AND DATE(c.created_on) > DATE_SUB(CURDATE(), INTERVAL 1 DAY)
AND c.group_id = " . $group_id . "
AND c.user_id != " . $user_id . "
UNION
SELECT r.* FROM readsbaby r
LEFT JOIN comments c ON c.id = r.notification_id
WHERE
c.id IS NULL
AND r.group_id = " . $group_id . "
AND r.user_id != " . $user_id . "
这可能需要进行调整以适应,因为几乎没有什么可以解释数据和表的关系。
注意:不要像以前那样注入 SQL。 考虑使用 PDO 或任何其他预准备语句(例如 http://php.net/manual/en/pdo.prepare.php)参数化查询