asked on
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.
Microsoft SQL Server
Last Comment
appari
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.