counting between and comparing between rows

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:

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                       

more ▼

asked Oct 30, 2009 at 12:18 PM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

    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            

more ▼

answered Oct 30, 2009 at 04:25 PM

avatar image

320 3 5 8

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 30, 2009 at 12:18 PM

Seen: 1377 times

Last Updated: Nov 08, 2009 at 11:22 AM

Copyright 2018 Redgate Software. Privacy Policy