时间戳

首页 > 解决方案 > NOT EXISTS 未在结果中考虑

php - NOT EXISTS 未在结果中考虑

问题描述

我正在尝试从“评论”表中获取结果，但表中的结果除外'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关闭过滤的结果。

标签： phpmysql

解决方案

我会以这种方式更改查询，以便在表 readsbaby 中只有没有任何匹配的行：

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();

}

推荐阅读