Avatar of ams_group
ams_group
 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.
Microsoft SQL Server

Avatar of undefined
Last Comment
appari

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Aaron Tomosky

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
appari

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck