SQL联接问题 [英] Problem with sql joins

问题描述

你好，

我有3张桌子.第一个表具有所有记录，第二个表具有与记录相关的注释，最后一个表具有与所有注释相关的答复.
我想要的是显示所有带有注释和回复的记录.
每个记录可以有0到n条注释.答复也一样.

我的存储过程如下:

Hello,

I have 3 tables. 1 st table has all records, 2nd one has comments related to records and last one has replies related to all comments.
What i want is to display all records with comments and replies.
There can be 0 to n comments for each records. Same for replies.

My stored procedure is like:

ALTER PROCEDURE [dbo].[proc_ShowOpenSII]

AS  
BEGIN  
	SELECT 
		SII.SII_Id,
		SII.Title, 
		SII.Added_When,
		SII.Added_By,
		SII.Business_Owner,
		SII.Implementing_Cost,
		St.[Status],
		(Comm.Comments+'' ''+Comm.Comment_Added_By+'' ''+Rep.Replies+'' ''+Rep.Reply_Added_By) AS Notes
	FROM 
		Service_Improvement_Initiative AS SII 
		INNER JOIN Statuses AS St ON SII.Status_Id=St.Status_Id
		LEFT JOIN SII_Comments AS Comm ON SII.SII_Id=Comm.SII_Id
		LEFT JOIN SII_Replies AS Rep ON Comm.SII_Comment_Id=Rep.SII_Comment_Id
	WHERE
		SII.Status_Id <> ''9''
END

但是我两次获得带评论的记录..
此查询可能有什么问题?

But i am getting records with comments twice..
What can be wrong with this query?

推荐答案

这是因为您将其与"replies"表一起使用.您应该首先获得所有评论，然后再对该评论进行回复.
如果重复将消失，请尝试删除答复表上的联接查询.

It is because you join it with replies table. you should first get all the comments first and then the replies on that comment.
Try by removing the join query on the replies table if the duplication would be gone.

您正在使用SII_Comments，而SII_Replies在单个查询中同时对两个表进行了评论. .......

you are using SII_Comments , and SII_Replies both tables in single Query that''s by you are getting twice comments .......

您是否曾在select语句后尝试使用Distinct?

Had you tried to use Distinct after select statement?

这篇关于SQL联接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！