mySQL 从两个具有相互条件的不同表进行查询

Question

请帮助我构建 mysql 查询

我有 2 个表，#_udjacomment 和 #_content

目前我有查询：

$query  = "SELECT udja.id";

      if( $include_author == 1 ) $query .= ", udja.full_name";
      if( $include_date == 1 ) $query .= ", udja.time_added";
      if( $include_comment == 1 ) $query .= ", if(CHAR_LENGTH(udja.content) > ".$content_number_of_characters.", SUBSTR(udja.content, 1, ".$content_number_of_characters."), udja.content) AS content";
      if( $include_link_to_comment == 1 ){ 
        $query .= ", CASE WHEN LOCATE('com_content:', udja.comment_url) > 0 
        THEN CONCAT(SUBSTRING_INDEX(udja.comment_url,':',-1),'-', com_content.alias, '.html')
        ELSE udja.comment_url END AS comment_url";
        }

      $query .= " FROM #__udjacomments AS udja, #__content AS com_content WHERE udja.is_published = 1 AND com_content.id = SUBSTRING_INDEX(udja.comment_url,':',-1) AND com_content.checked_out = 0 ORDER by udja.id DESC limit ".$number_of_comments;

但我没有得到正确的结果。如果我停止尝试从表 #__content AS com_content 访问，那么我会得到 #__udjacomment AS udja 正确的结果

所以，我想我是在问如何指示并包含我想要的字段 com_content.alias 的约束WHERE com_content.id = SUBSTRING_INDEX(udja.comment_url,':',-1)

在某些情况下，udja.comment_url 将具有此格式com_content:22、com_content:19

在其他情况下，udja.comment_url 将有一个像 word-word-another-word 这样的字符串，

这就是为什么我在条件 if($include_link_to_comment == 1) 中有更广泛的语句

更新：THE最终查询如下所示（我实施了答复者的建议并更改了案例陈述和WHERE 语句）

 $query  = "SELECT udja.id";

      if( $include_author == 1 ) $query .= ", udja.full_name";
      if( $include_date == 1 ) $query .= ", udja.time_added";
      if( $include_comment == 1 ) $query .= ", if(CHAR_LENGTH(udja.content) > ".$content_number_of_characters.", SUBSTR(udja.content, 1, ".$content_number_of_characters."), udja.content) AS content";
      if( $include_link_to_comment == 1 ){ 
        $query .= ", CASE 
            WHEN LOCATE('com_content:', udja.comment_url)<>0
            THEN CONCAT(SUBSTRING_INDEX(udja.comment_url,':',-1),'-', com_content.alias, '.html')
            ELSE udja.comment_url 
            END AS comment_url";

        }
//          THEN CONCAT(SUBSTRING_INDEX(udja.comment_url,':',-1),'-', com_content.alias, '.html')
      $query .= " FROM #__udjacomments AS udja
                    LEFT JOIN #__content AS com_content 
                        ON com_content.id = SUBSTRING_INDEX(udja.comment_url,':',-1) 
                    WHERE udja.is_published = 1 ORDER by udja.id DESC limit ".$number_of_comments;

Answer 1

您需要使用外连接：

...
FROM #__udjacomments AS udja
LEFT JOIN #__content AS com_content 
    on com_content.id = SUBSTRING_INDEX(udja.comment_url,':',-1)
WHERE ...