Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Query matching datasets

Posted on 2012-03-18
2
Medium Priority
?
454 Views
Last Modified: 2012-04-11
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.
0
Comment
Question by:ams_group
2 Comments
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 1500 total points
ID: 37736173
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?
0
 
LVL 39

Expert Comment

by:appari
ID: 37736197
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question