Solved

SQL Server Query matching datasets

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 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