February 20, 2013 at 4:39 pm
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.
February 21, 2013 at 8:04 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply