Matching Strings In Different Tables Of Same Database

I have a situation where I want to pull strings from one table of a SQL 2000
database and find matches for it in other tables of the same database and
have those values returned. i.e. In one table I have prospects and I want
to match their names to a table that stores the names of prospects turned
into customers. I want to write a query that looks through every entry and
returns a match for each corresponding value (from prospects to customers).
So if "Smith" is found in prospects I want SQL to return "Smith" in
customers with full contact info.

Any pointers on getting started on this is greatly appreciated. Or if you
could just point me to a reference. Obviously, I need to do some kind of
parsing. I just need to be pointed in the right direction.

Thx."Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon|||"Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon