x

Match and Unmatach Records

I have 3 sets of Data from 3 Database sources. My question is how will I find which records match and which did not? its okay to have Nulls in Master table

Table 1:

ID Name

1 AAA

2 BBB

3 CCC

4 DDD

5 EEE

6 FFF

Table 2:

ID City

1 NYC

2 LA

3 SFA

7 ATL

8 HTOWN

100 SJC

Table 3:

ID SSN

3 1111

4 1112

7 1113

5 1114

100 1115

6 1116

Master Table should look something like this (Its OKAY TO HAVE NULLS)

ID Name City SSN

1 AAA NYC NULL

2

3

4

5

6

7

8

100 NULL NULL 1115

YOUR HELP WILL BE GREATLY APPRECIATED?

more ▼

asked Feb 02, 2010 at 12:37 AM in Default

avatar image

InwoodGuy
44 7 8 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

;WITH cte AS(
SELECT ID, Name, NULL [City], NULL [SSN] FROM tab1
UNION ALL SELECT ID, NULL, City, NULL FROM tab2
UNION ALL SELECT ID, NULL, NULL, SSN FROM tab3
)
SELECT ID, MAX(cte.Name)[Name], MAX(cte.City)[City], MAX(cte.SSN)[SSN]
FROM cte
GROUP BY ID
more ▼

answered Feb 02, 2010 at 01:43 AM

avatar image

Scot Hauder
6.4k 13 16 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x428
x153

asked: Feb 02, 2010 at 12:37 AM

Seen: 1339 times

Last Updated: Feb 02, 2010 at 10:23 AM

Copyright 2016 Redgate Software. Privacy Policy