Solved

SQL Server Query matching datasets

Posted on 2012-03-18
2
434 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 38

Accepted Solution

by:
Aaron Tomosky earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now