How to get rid of the duplicates in inner join in SQL Server

I am joini开发者_JAVA技巧ng 3 tables Opportunity, Lead, Notes

Select 
     Distinct O.oppo_jobid, O.oppo_status,
     cast(N.Note_Note as NCHAR) as Notes 
from Opportunity O, Notes N
Inner join Notes on o.Oppo_OpportunityId = Notes.Note_ForeignId 
Inner join Lead on o.Oppo_OpportunityId = Lead.Lead_OpportunityID

I am getting duplicates. How to avoid duplicates while using inner join?

Main table is Opportunity and Notes are compared with opportunity id and Notes table has many records with same opportunity in so the duplicate occurs. Now how I can avoid duplicates while comparing both table and fetching last updated note.

Thanks in advance...

Make sure you don't accidently do an cartesian product here:

from Opportunity O, Notes N

This should be only

from Opportunity O

You're already INNER joining the Notes table later....

So this query here should hopefully not return any duplicates:

Select 
     O.oppo_jobid, O.oppo_status,
     cast(N.Note_Note as NCHAR) as Notes 
from dbo.Opportunity O
Inner join dbo.Notes on o.Oppo_OpportunityId = Notes.Note_ForeignId 
Inner join dbo.Lead on o.Oppo_OpportunityId = Lead.Lead_OpportunityID

Update: if you want only the most recent note for each opportunity - use this query:

;WITH MostRecent AS
(
    SELECT 
        O.oppo_jobid, O.oppo_status,
        cast(N.Note_Note as NCHAR) as Notes,
        ROW_NUMBER() OVER (PARTITION BY o.Oppo_OpportunityId 
                           ORDER BY n.Note_DateTimeStamp DESC) AS 'RowNum'
    FROM dbo.Opportunity O
    INNER JOIN dbo.Notes on o.Oppo_OpportunityId = Notes.Note_ForeignId 
)
SELECT *
FROM MostRecent
WHERE RowNum = 1