Thank you for the feedback! Lynn

Here's the simplified version of my table Structure:

GroupID col1 col2 col3 col4

1 as er v null

2 as er v null

1 df gh null v/

2 df gh bn m

2 vf fg c b

1 fg gh c b

Between GoupID 1 and 2

The output for "Only records that are equal" will be:

GroupId col1 col2 col3 col4

1 as er v null

' 2 as er v null' can't be shown since all the fields other than the GroupID are duplicates.

The output for "Only Records that are different" will be:

GroupId col1 col2 col3 col4

1 df gh null v/

2 df gh bn m

Good to have feature will be mark which column the difference is from, and he difference will always comes from either col3 or col4

The output for "Only records not exist in all Groups will be:

GroupId col1 col2 col3 col4

2 vf fg c b

1 fg gh c b

From above you can see that col1 and col2 combined to determine one record, the difference comes from either col3 or col4 between two GroupID

the output has to show all 5 columns.

The query needs to be able to compare three or more GroupIDs.

I would be really appreciate if anyone can come up an efficient query for the bottom two outputs. I was able to do the first one.

Thank you!

Here's my query for the first output:

Select min(GroupID) as GroupID, col1,col2,col3,col4

from

(Select GroupID, col1, col2, col3. col4

from table

where GroupID = 1

Union all

Select GroupID, col1, col2, col3. col4

from table

where GroupId = 2)

Group by col1, col2, col3, col4

Having count(*) > 1