MySQL Forums
Forum List  »  Full-Text Search

Match a List Table against an Alias Table
Posted by: Michael Shipe
Date: August 03, 2010 09:05PM

I'm stumped on a problem. Hope the MySQL community can shed some light for me.

I'm trying to take the company names from the first table, match them against an alias table and use the Company_ID from the alias table to insert the results into a third table.

(Please pardon my poorly formated ascii tables)
1st Table: list
+---------------+
|Company_Name |
+---------------+
|IBM |
+---------------+
|Hewlett Packard|
+---------------+
|adidas ag |
+---------------+

2nd Table: alias
+---------------+------------+----------+------------------------------+
|Company_ID |alias1 |alias2 |alias3 |
+---------------+------------+----------+------------------------------+
|100 |IBM |IBM Co. |International Business Machine|
+---------------+------------+----------+------------------------------+
|101 |HP |HP Company|Hewlett Packard |
+---------------+------------+----------+------------------------------+
|102 |adidas |adidas ag.|Adidas Group |
+---------------+------------+----------+------------------------------+

Currently I accomplish this with the following query:

SELECT `alias`.*, `list`.*
FROM `validate_temp`, `alias_table`
WHERE (company_name = alias1
OR company_name = alias2
OR company_name = alias3)
ORDER BY company_name

The result would be
+-----------+-------------+
|Company_ID |Company_Name |
+-----------+-------------+
|100 |IBM |
+-----------+-------------+
|101 |HP +
+-----------+-------------+

Adidas would not match because 'adidas ag' does not equal alias2 'adidas ag.'

Actually this method is full of problems because different lists use HP, HP Co., HP Company, Hewlett Packard Company, Hewlett-Packard Company, Hewlett-Packard Co, Hewlett-Packard Co., etc. etc. etc.

So I am searching for a better way to match my list table (which will be ever changing as I find new lists of companies) with my alias table so that I'm sure I I get the correct Company_ID.

Thanks so much for your help!

Options: ReplyQuote


Subject
Views
Written By
Posted
Match a List Table against an Alias Table
4935
August 03, 2010 09:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.