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

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.

Matching Debits and Credits query

I have a SQL statement that matches credit amounts to debit amounts which works fine except in cases when there is more than one debit amount for the same value, the code then applies the 1 credit to all the debits of the same amount.

eg. if I have the following

Amount Inv. No.
$200.00 345
$300.00 567
$200.00 129

In the above example using the code below, if I have a credit of $200.00 it will be applied to both inv nos. 345 and 129. I would just like it applied to either 345 OR 129.

SELECT Pos.osamt, Pos.ClientNo, Pos.FirstName, Pos.LastName, Pos.Address1, Pos.Address2, Pos.City, Pos.Country, Pos.TransDate, Pos.InvoiceNo, Pos.PolicyNo, Pos.TransAmt, Pos.TaxAmt, Pos.ReceiptAmt, Pos.Currency
FROM Pos LEFT JOIN Neg ON (Pos.osamt = abs(Neg.osamt)) AND (Pos.ClientNo = Neg.ClientNo)
WHERE (((Neg.osamt) Is Null));

Any help would be appreciated.Your design is extremely flawed, matching debits to credits by amount only can result in applying the incorrect credit/debit combination even if the client# is the same.

:rolleyes:|||That's only part of it. I just need it resolved.|||I would just like it applied to either 345 OR 129.
Well, it is YOU who has to know whether it will be 345 or 129. What happens when there's another Inv. no with the same credit ($200)? And another?

There, of course, is a way - you could, for example, choose MIN(inv_no) or MAX(inv_no) or ...

Data model is awful; you'd better change it (if you can), because this promises you only sweat, blood and tears.|||...just out of curiosity how do you propose to match a payment against an invoice where the payer is disputing part of the invoice (ie they are paying some, but not all of the invoice), or where the payer makes a single payment covering many invoices?