[mysql]理解内部联接或左联接的问题

标签: MySQL
发布时间: 2017/1/9 22:53:19
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我正在理解内在和左联接问题

我有在 outsystems 查询下

 SELECT {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments]
      , {CLD}.[LastUpdateOn],min({Project}.[Number])
      ,count({Project}.[Number])
 FROM {CLD}
     INNER JOIN {Project} ON  {Project}.[Id] = {CLDProjects}.[ProjectId] 
     INNER JOIN {CLDProjects} ON {CLD}.[Id] = {CLDProjects}.[CLDId]
 WHERE 
   (
     @IsJAXPM =1 
     or EXISTS (SELECT 1 
             FROM {CLDParticipant} 
             WHERE {CLDParticipant}.[CLDId] = {CLD}.[Id] 
               AND {CLDParticipant}.[UserId] = @UserId) 
     or EXISTS (SELECT 1 
                FROM {ProjectParticipantWidget} 
                    INNER JOIN {ProjectParticipant} ON {ProjectParticipantWidget}.[ProjectParticipantId] =  {ProjectParticipant}.[Id]
                WHERE {ProjectParticipant}.[ProjectId] = {Project}.[Id] 
                  AND {ProjectParticipant}.[UserId] = @UserId)
   )
 GROUP BY {CLD}.[Id], {CLD}.[Name], {CLD}.[Comments], {CLD}.[LastUpdateOn]

问题是选择牵着根据该项目的所有 CLD 元素,我想选择 CLD 的其项目 id = Project.Id。我试过两个连接,但它保持拉下面如何结构看起来像所有的值enter image description here

解决方法 1:

请尝试下列操作︰ 第一次得到匹配的项目的 CLDProjects 然后得到从 CLD 匹配记录。

FROM {CLD} INNER JOIN (
    {CLDProjects} INNER JOIN {Project} ON  {Project}.[Id] = {CLDProjects}.[ProjectId]
  ) ON {CLD}.[Id] = {CLDProjects}.[CLDId]
赞助商