SQL Server Query matching datasets
Hi,
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
NULL NULL NULL AAA 111 A1A
NULL NULL NULL AAA 111 A1A
NULL NULL NULL 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
output
1 AAA 111 A1A 1 AAA 111 A1A
2 AAA 111 A1A 2 AAA 111 A1A
NULL NULL NULL 3 AAA 111 A1A
NULL NULL NULL 4 AAA 111 A1A
NULL NULL NULL 5 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.
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
NULL NULL NULL AAA 111 A1A
NULL NULL NULL AAA 111 A1A
NULL NULL NULL 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
output
1 AAA 111 A1A 1 AAA 111 A1A
2 AAA 111 A1A 2 AAA 111 A1A
NULL NULL NULL 3 AAA 111 A1A
NULL NULL NULL 4 AAA 111 A1A
NULL NULL NULL 5 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?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month9 days, 22 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
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?