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

Trisha gravatar 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

Benjamin gravatar image

320 2 3 4

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1278 times

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