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?