SQL Server Query matching datasets


I am trying to join 2 sets of data unsuccesfully due to the desired output i am after i.e.

each data set has 3 columns of data i can join on i.e.

data set 1                                Data set 2

AAA  111  A1A                       AAA  111  A1A
AAA  111  A1A                       AAA  111  A1A
BBB  222  B2B                       AAA  111  A1A
CCC  333   C3C                       AAA  111  A1A
                                              AAA  111  A1A
                                              BBB  222  B2B    
                                              CCC  333   C3C  

The output I am after is:

AAA   111   A1A    AAA  111  A1A
AAA   111   A1A    AAA  111  A1A
BBB   222    B2B   BBB  222  B2B  
CCC    333    C3C   CCC  333   C3C  

If i try left outer or full outer joins i just end up with duplicates etc. I am not sure if the output i am after is possible (my SQL skills aren't amazing). I did think of trying to add uniqueness to each and row and match on that but not sure how i would automate adding a count to each group of duplicates to do this either.

1 AAA  111  A1A                      1 AAA  111  A1A
2 AAA  111  A1A                      2 AAA  111  A1A
1 BBB  222  B2B                      3 AAA  111  A1A
1 CCC  333   C3C                      4 AAA  111  A1A
                                                5 AAA  111  A1A
                                                1 BBB  222  B2B    
                                                1 CCC  333   C3C  


1 AAA   111   A1A    1 AAA  111  A1A
2 AAA   111   A1A    2 AAA  111  A1A
1 BBB   222    B2B   1 BBB  222  B2B  
1 CCC    333    C3C   1 CCC  333   C3C  

Any help much appreciated, applogies if i have overlooked an easy obvious solution to this.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aaron TomoskyDirector of Solutions ConsultingCommented:
You can't even delete an individual duplicated row from one of those data sets withou adding a unique id.
I don't think there is a way even of you join all the columns. I dot see how the nulls will happen since there are two rows with those exact values. Rows don't get "used up" when they are matched.

You could possibly make a proc and make temp tables from both datasets, fill a temp table with matched rows deleting from the first two temp tables as you go, then select that out as the result set. Seems really really ugly messy to me.

You could also just group by all colums with a count, then join on all three columns and show the count of matches for each data set. Then you could add a column with the difference and even choose to only show rows.with that difference... Would that work?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
how are you generating these datasets? if it is generated using SQL we can try to generate the unique ids using sql and even generate the final joined data in sql. post the existing sql.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.