mysql - 内部连接函数重复内容

Question

我的查询重复了我得到的条目。我使用两个内部连接。奇怪的是，我在 6 个月前编写了这段代码，据我所知，它有效。现在，我回到我的项目，它不再工作了(重复)...

查询:

$sql_principale_news = "SELECT sys_pre_values.LKey,bx_blogs_posts.PostUri,bx_blogs_posts.PostCaption,bx_blogs_posts.PostText,bx_blogs_posts.PostText,bx_blogs_posts.Categories,
      bx_blogs_posts.PostDate,bx_blogs_posts.Views,bx_blogs_posts.CommentsCount,bx_blogs_posts.allowView,Profiles.Avatar,Profiles.ID,Profiles.FirstName,Profiles.LastName,Profiles.ProfileType
      FROM bx_blogs_posts
      INNER JOIN Profiles ON bx_blogs_posts.OwnerID=Profiles.ID AND Profiles.Status='Active'
      INNER JOIN sys_pre_values ON bx_blogs_posts.NewsType=sys_pre_values.Value AND sys_pre_values.Value!=sys_pre_values.LKey
      WHERE bx_blogs_posts.OpenNews=1 
        AND bx_blogs_posts.PostStatus='approval'
        AND bx_blogs_posts.Categories IN ('".$innov24_config."')
        AND (bx_blogs_posts.allowView=3 
          OR bx_blogs_posts.allowView=4 
          OR (bx_blogs_posts.allowView=5 AND bx_blogs_posts.OwnerID IN ('".$bigfriendlist."')) 
          OR bx_blogs_posts.allowView IN ('".$privacy_OthersToMe."') 
          OR bx_blogs_posts.allowView IN ('".$privacy_MeToOthers."'))
      ORDER BY bx_blogs_posts.PostDate DESC LIMIT ".$_GET['debut'].",".$nb_affichage_par_page."";

结果:

它将每个条目复制 3 次。

问题

如果我删除第二个内部联接，则不再有重复项。

$sql_principale_news = "SELECT sys_pre_values.LKey,bx_blogs_posts.PostUri,bx_blogs_posts.PostCaption,bx_blogs_posts.PostText,bx_blogs_posts.PostText,bx_blogs_posts.Categories,
      bx_blogs_posts.PostDate,bx_blogs_posts.Views,bx_blogs_posts.CommentsCount,bx_blogs_posts.allowView,Profiles.Avatar,Profiles.ID,Profiles.FirstName,Profiles.LastName,Profiles.ProfileType
      FROM bx_blogs_posts
      INNER JOIN Profiles ON bx_blogs_posts.OwnerID=Profiles.ID AND Profiles.Status='Active'
      // DELETED CODE // INNER JOIN sys_pre_values ON bx_blogs_posts.NewsType=sys_pre_values.Value AND sys_pre_values.Value!=sys_pre_values.LKey// END DELETED CODE //
      WHERE bx_blogs_posts.OpenNews=1 
        AND bx_blogs_posts.PostStatus='approval'
        AND bx_blogs_posts.Categories IN ('".$innov24_config."')
        AND (bx_blogs_posts.allowView=3 
          OR bx_blogs_posts.allowView=4 
          OR (bx_blogs_posts.allowView=5 AND bx_blogs_posts.OwnerID IN ('".$bigfriendlist."')) 
          OR bx_blogs_posts.allowView IN ('".$privacy_OthersToMe."') 
          OR bx_blogs_posts.allowView IN ('".$privacy_MeToOthers."'))
      ORDER BY bx_blogs_posts.PostDate DESC LIMIT ".$_GET['debut'].",".$nb_affichage_par_page."";

谁能说说为什么？

[编辑] 这是表格结构

Answer 1

你可以使用 GROUP BY

试试这个

   $sql_principale_news = "SELECT sys_pre_values.LKey,bx_blogs_posts.PostUri,bx_blogs_posts.PostCaption,bx_blogs_posts.PostText,bx_blogs_posts.PostText,bx_blogs_posts.Categories,
  bx_blogs_posts.PostDate,bx_blogs_posts.Views,bx_blogs_posts.CommentsCount,bx_blogs_posts.allowView,Profiles.Avatar,Profiles.ID,Profiles.FirstName,Profiles.LastName,Profiles.ProfileType
  FROM bx_blogs_posts
  INNER JOIN Profiles ON bx_blogs_posts.OwnerID=Profiles.ID AND Profiles.Status='Active'
   INNER JOIN sys_pre_values ON bx_blogs_posts.NewsType=sys_pre_values.Value AND sys_pre_values.Value!=sys_pre_values.LKey// END DELETED CODE //
  WHERE bx_blogs_posts.OpenNews=1 
    AND bx_blogs_posts.PostStatus='approval'
    AND bx_blogs_posts.Categories IN ('".$innov24_config."')
    AND (bx_blogs_posts.allowView=3 
      OR bx_blogs_posts.allowView=4 
      OR (bx_blogs_posts.allowView=5 AND bx_blogs_posts.OwnerID IN ('".$bigfriendlist."')) 
      OR bx_blogs_posts.allowView IN ('".$privacy_OthersToMe."') 
      OR bx_blogs_posts.allowView IN ('".$privacy_MeToOthers."'))
 GROUP BY sys_pre_values.LKey
  ORDER BY bx_blogs_posts.PostDate DESC LIMIT ".$_GET['debut'].",".$nb_affichage_par_page."";