Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

compare the value in a column against multiple databases Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 4:39 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, September 25, 2014 5:30 PM
Points: 442, Visits: 813
Using SQL 2000

I have a table in Database A, lets call it customer.
In the customer table there are several columns but I am interested in customer_name and address and city

I want to do a comparison against similar tables in other databases.

Sample TSQL

Select A.customer_name, A.address, A.city, B.customer_name, B.address, b.city, c.customer_name, c.address, c.city
from customer as A
join DatabaseB.dbo.customer as B on left(A.customer_name,18) = left(B.customer_name,18)
join DatabaseC.dbo.customer as C on left(A.customer_name,18) = left(B.customer_name,18)


The above is for example only and may not be entirely correct.

My challenge is
I want to return to my result set one record for each possible match based on the best match.

Meaning if Database B matches exactly on customer_name and address and city but Database C does not then only return database B's results.

I don't know if this is possible but if anyone can show me how to get the results I need I would greatly appreciate it.



Post #1422353
Posted Thursday, February 21, 2013 8:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 317, Visits: 1,079
I haven't got SQL2000, but I think this will work.

Assuming I've understood the problem, ie it's the comparison & not the fact it's across databases...

I've used 3 tables on the same DB, but it should work across databases.

You could end up with 2 nulls if neither B or C match...



--== Sample Data ==--
use tempdb
go

if object_id('tempdb..#Customer1') is not null drop table #Customer1
if object_id('tempdb..#Customer2') is not null drop table #Customer2
if object_id('tempdb..#Customer3') is not null drop table #Customer3

create table #Customer1
(
customer_name varchar(30),
address varchar(30),
city varchar(30)
)

insert into #Customer1 values ('Joe James', '43 Holgate St', 'York')
insert into #Customer1 values ('Ken Fletcher', '15 North St', 'Leeds')

create table #Customer2
(
customer_name varchar(30),
address varchar(30),
city varchar(30)
)

insert into #Customer2 values ('Joe James', 'Holgate St', 'York')
insert into #Customer2 values ('Ken Fletcher', '15 North St', 'Leeds')

create table #Customer3
(
customer_name varchar(30),
address varchar(30),
city varchar(30)
)

insert into #Customer3 values ('Joe James', '43 Holgate St', 'York')
insert into #Customer3 values ('Ken Fletcher', 'North St', 'Leeds')

--== Example to show result from joins ==--
Select
A.customer_name, A.address, A.city,
B.customer_name, B.address, b.city,
c.customer_name, c.address, c.city
from #Customer1 as A
left join #Customer2 as B on left(A.customer_name,18) = left(B.customer_name,18)
and A.Address = B.Address and A.City = B.City
left join #Customer3 as C on left(A.customer_name,18) = left(C.customer_name,18)
and A.Address = C.Address and A.City = C.City

--== Possible Solution ==--
Select
ISNULL(B.customer_name, C.customer_name) as customer_name,
ISNULL(B.address, C.address) as address,
ISNULL(B.city, C.city) as city
from #Customer1 as A
left join #Customer2 as B on left(A.customer_name,18) = left(B.customer_name,18)
and A.Address = B.Address and A.City = B.City
left join #Customer3 as C on left(A.customer_name,18) = left(C.customer_name,18)
and A.Address = C.Address and A.City = C.City




Post #1422611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse