Click here to Skip to main content
15,936,337 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
how can remove duplicate rows in below procedure

in TBLTicketAnswers i have some record with same TicketID and i don't want to display just one row:
SELECT  TBLTickets.TicketID ,
                           TBLTickets.UserID ,
                           TBLTickets.AttachFile ,
                           TBLTickets.HasFile ,
                           TBLTickets.Title ,
                           TBLTickets.Question ,
                           TBLTickets.Flag ,
                           TBLTickets.InsertDate ,
                           TBLTicketBranchs.BranchName ,
                           TBLTicketAnswers.AnswerID ,
                           TBLTicketAnswers.Answer ,
                           TBLUsers.UserName ,
                           TBLUsers.UserOwner ,
                   FROM    dbo.TBLTicketBranchs
                           INNER JOIN dbo.TBLTickets ON dbo.TBLTicketBranchs.BranchID = dbo.TBLTickets.BranchID
                           INNER JOIN dbo.TBLUsers ON dbo.TBLTickets.UserID = dbo.TBLUsers.UserID
                           LEFT OUTER JOIN dbo.TBLTicketAnswers ON dbo.TBLTickets.TicketID = dbo.TBLTicketAnswers.TicketID
                   WHERE   ( TBLTicketBranchs.ResellerID = @ResellerID   --without flag (@flag=0)
                             AND ( TBLTicketAnswers.Answer LIKE N'%'
                                   + @Keyword + '%'
                                   OR @Keyword IS NULL
                                   OR TBLTickets.Title LIKE N'%' + @Keyword
                                   + '%'
                                   OR @Keyword IS NULL
                                   OR TBLTickets.Question LIKE N'%'
                                   + @Keyword + '%'
                                   OR @Keyword IS NULL
                                   OR TBLUsers.UserName LIKE N'%' + @Keyword
                                   + '%'
                                   OR @Keyword IS NULL

Updated 28-Jul-13 1:48am
Maciej Los 28-Jul-13 15:28pm    
We can't read in your mind ;(
CHill60 28-Jul-13 19:10pm    
Adarsh chauhan 29-Jul-13 4:28am    
sorry but your question is not clear to me... what do you mean by
"i have some record with same TicketID and i don't want to display just one row"??
if prob. is with duplicate records then you can use Distinct.. else please explain your prob.

1 solution

Try this....
SELECT Distinct  ti.TicketID, ti.UserID, ti.AttachFile, ti.HasFile, ti.Title, ti.Question, ti.Flag, ti.InsertDate, tb.BranchName, ta.AnswerID, ta.Answer, tu.UserName, tu.UserOwner, tu.Corporation
FROM  dbo.TBLTicketBranchs tb
INNER JOIN dbo.TBLTickets ti ON tb.BranchID = ti.BranchID
INNER JOIN dbo.TBLUsers tu ON ti.UserID = tu.UserID
LEFT OUTER JOIN dbo.TBLTicketAnswers ta ON ti.TicketID = ta.TicketID
WHERE   tb.ResellerID = @ResellerID   --without flag (@flag=0)
AND ( ta.Answer LIKE N'%' + @Keyword + '%'
      OR @Keyword IS NULL
      OR ti.Title LIKE N'%' + @Keyword + '%'
      OR @Keyword IS NULL
      OR ti.Question LIKE N'%' + @Keyword + '%'
      OR @Keyword IS NULL
      OR tu.UserName LIKE N'%' + @Keyword + '%'
      OR @Keyword IS NULL

Try using Alias Names While Writing Joins...
Share this answer
Adarsh chauhan 1-Aug-13 2:28am    
I agree... Distinct would work for this... and its always better to use alias names for tables when you have to join them.. :) +5
Raja Sekhar S 1-Aug-13 2:40am    
Thank you... Adarsh...

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900