troubleshooting Question

SQL Server Query matching datasets

Avatar of ams_group
ams_group asked on
Microsoft SQL Server
2 Comments1 Solution519 ViewsLast Modified:
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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros