Click here to Skip to main content
14,732,787 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 ,
                           TBLUsers.Corporation
                   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
                                 )
                           )


TBLTicketAnswers
-----------------
AnswerID
Answer
TicketID
....
..
Posted
Updated 28-Jul-13 2:48am
v2
Comments
Maciej Los 28-Jul-13 15:28pm
   
We can't read in your mind ;(
CHill60 28-Jul-13 19:10pm
   
Distinct
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...
   
Comments
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