Remove duplicate values in FULL OUTER JOIN

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008

, +

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:

SQL

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 28-Jul-13 1:44am

jiji2663

Updated 28-Jul-13 1:48am

v2

Add a Solution

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

Add a Solution

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

Raja Sekhar S · Answer 1 · 2013-07-31T20:22:00

Try this....

SQL

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...

Remove duplicate values in FULL OUTER JOIN

1 solution

Solution 1

Add your solution here

Preview 0

Remove duplicate values in FULL OUTER JOIN

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us