counting between and comparing between rows

Question

I have a similar list to below........ although very lengthy, where the names for the id's come from different sources (indicated by 'other Dim') and for the same ID, the list might have the name not the same in each row. misspelled, or a space in it somewhere that would make it a diff value for reporting. What I need to get returned are the rows where the count of that ID is more than 1, and within the rows for that ID the names don't all match.
(if there is only one row for that id, or if all of the 2+ names match exactly, don't want it returned) I am at a loss, I want it to group by the ID, count that, having >1, which I am not even sure I am getting right...... but then also compare within each grouping of the ID, but between rows.
Help plz!

Sample Data:

                    
otherDim.......ID.....Name                    
108            A80    Trisha                      
456            A80    Trisha                      
878            A80    Triosha                      
456            G21    Simon                         
108            B55    Jessi                      
456            B55    Jessi                      
878            B55    Jessi                      
108            ZZZ    Mark                      
456            ZZZ    M ark                      
878            ZZZ    Ma rk                      
108            F42    Suzie                      
456            F42    Suzie Q                       
878            F42    Suzie                      
108            W88    Josh                      
456            W88    Josh                      
878            W88    Josh

Answer 1

Basically perform a self join (INNER JOIN same table to itself) where the IDs match but the Names do not match. Just replace the [Sample_Data] with the name of your table in both places in the query.

SELECT            
    a.[ID],            
    a.[Name] AS [NameA],            
    a.[otherDim] AS [otherDimA],            
    b.[Name] AS [NameB],            
    b.[otherDim] AS [otherDimB]            
FROM [Sample_Data] a            
INNER JOIN [Sample_Data] b            
ON a.[ID] = b.[ID] AND a.[Name] <> b.[Name]            
WHERE a.Name < b.Name --basically this prevents duplicate matches

That produces a result set like this. So notice you get Triosha 878 and it's non-match Trisha 108 and you also get another line with it's other non-match Trisha 456.

ID  NameA   otherDimA	NameB	otherDimB            
A80 Triosha	878	Trisha	108            
A80 Triosha	878	Trisha	456            
ZZZ M ark	456	Mark	108            
ZZZ Ma rk	878	Mark	108            
ZZZ M ark	456	Ma rk	878            
F42 Suzie	108	Suzie Q	456            
F42 Suzie	878	Suzie Q	456

counting between and comparing between rows

Follow this question

Related Questions