CODEWITHSUNDEEP
sql-server
coderwill
coderwill

Reputation: 844

How to stop repeated data using sql query in stored procedure?

I am create a stored procedure in SQL Server for showing feed with also feed comment but I am creating this stored procedure using repeated data also show here below I have listed my table with data any one please help me how can show my satisfaction data using SQL Server.

Feed table : 

    FeedID | userId  | Caption | TotalComments | InsertDateTime

    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

FeedComment table :

    FeedCommentID | FeedID | UserId | Comment | InsertDateTime

    1                  1      102      Good          2017-05-04 18:51:23.960
    2                  1      103      Bad           2017-05-04 16:51:23.960
    3                  2      102      Very Good     2017-05-04 18:51:23.960
    4                  1      101      ....          2017-05-04 14:51:23.960

Expected output:

    FeedID | userId  | Caption | TotalComments | InsertDateTime

    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

With my query, I get this output:

    FeedID | userId  | Caption | TotalComments | InsertDateTime

    1         101          Test        3              2017-05-04 18:51:23.960
    1         101          Test        3              2017-05-04 18:51:23.960  
    1         101          Test        3              2017-05-04 18:51:23.960
    2         102          Party       2              2017-05-03 17:51:23.960
    3         103          event       3              2017-05-02 18:10:23.960

Here this is my table and data and below I have written this query but it does not return proper data : 

SELECT 
    C.FeedID, *,
    (SELECT COUNT(*) 
     FROM FeedComment 
     WHERE FeedID = C.FeedID ) AS TotalComments
FROM 
    Feed C
INNER JOIN 
    Users U ON C.UserId = U.UserId
-- here I use a left join that's not returning proper data; 
-- without this left join proer data is returned, but I want to 
-- write this join so any one know how can manage 
LEFT JOIN
    FeedComment CC ON CC.FeedID = C.FeedID  
ORDER BY 
    CC.InsertDateTime DESC  
    OFFSET (1- 1) * 20 ROWS
    FETCH NEXT 20 ROWS ONLY

Upvotes: 0

Views: 177

Answers (1)

SHD
SHD

Reputation: 409

Try this query,

SELECT C.FeedID,userId,MAX(CC.CaptionInsertDateTime) AS CaptionInsertDateTime,                 
           COUNT(*) AS TotalComments
    FROM Feed C
    INNER JOIN Users U ON C.UserId = U.UserId
    LEFT join FeedComment CC ON CC.FeedID = C.FeedID 
    GROUP BY C.FeedID,userId
    ORDER BY MAX(CC.InsertDateTime) DESC  OFFSET (1- 1) * 20 ROWS
    FETCH NEXT 20 ROWS ONLY

or

SELECT C.FeedID,*                 
       ,(SELECT COUNT(*) FROM FeedComment WHERE FeedID = C.FeedID ) AS TotalComments
       ,(SELECT MAX(InsertDateTime) FROM FeedComment CC WHERE CC.FeedID = C.FeedID) AS InsertDateTime
FROM Feed C
INNER JOIN Users U ON C.UserId = U.UserId
ORDER BY InsertDateTime DESC  OFFSET (1- 1) * 20 ROWS
FETCH NEXT 20 ROWS ONLY

Upvotes: 1

Related Questions