compare the value in a column against multiple databases

  • 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.

  • 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