Problem with sql joins

Question

0.00/5 (No votes)

See more:

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?

Posted 23-Feb-12 22:35pm

tejashri.gandhi

Updated 23-Feb-12 22:42pm

v2

Add a Solution

Comments

RAJI @Codeproject 24-Feb-12 4:50am

use distinct after select

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

graciax8 · Answer 1 · 2012-02-23T22:55:00

Solution 2

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.

Posted 23-Feb-12 22:55pm

graciax8

Comments

tejashri.gandhi 24-Feb-12 5:04am

Ok..I removed left join with replies table..but how will i get replies then?

graciax8 29-Feb-12 21:13pm

You will get that using the commentID.

SELECT * FROM SII_Replies WHERE Rep.SII_Comment_Id = ''

Manoj Kumar Choubey · Answer 2 · 2012-02-23T22:59:00

Solution 3

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

Posted 23-Feb-12 22:59pm

Manoj Kumar Choubey

Dharmenrda Kumar Singh · Answer 3 · 2012-02-23T22:53:00

Solution 1

Had you tried to use Distinct after select statement?

Posted 23-Feb-12 22:53pm

Dharmenrda Kumar Singh

Problem with sql joins

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Problem with sql joins

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us