How to compare 2 columns in different tables.

--_000_056CC34C3816524081738D64C9B2C76542A6FF1553VMBX113ihos te_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have about 120,000 organization names in table1 which I am trying to matc=
h against 75,000 organization names in table2 and see if they are a perfec=
t match, a partial match or don't match at all.

I was looking at Natural Language Full Text Searches, (http://dev.mysql.com=
/doc/refman/5.0/en/fulltext-natural-language.html) and it seems to be along=
the lines of what I'm looking for but not exactly. "By default, the MATCH(=
) h> function performs a natural language search for a string against a text =
collection. A collection is a set of one or more columns included in a FULL=
TEXT index. The search string is given as the argument to AGAINST(). For ea=
ch row in the table, MATCH() t-search.html#function_match> returns a relevance value; that is, a similar=
ity measure between the search string and the text in that row in the colum=
ns named in the MATCH() rch.html#function_match> list."

My question is, can the argument in AGAINST() refer to a column in another =
table or does it have to be a specific string you are searching for? If th=
e MATCH() function won't work, any suggestions on how else to compare table=
1.name against table2.name? The columns are defined as VARCHAR.

This is for MySQL 5.0, and the tables are MyISAM. They are fairly small tem=
porary tables created for one off comparison purposes, so performance is no=
t a big consideration.


JF


--_000_056CC34C3816524081738D64C9B2C76542A6FF1553VMBX113ihos te_--

Re: How to compare 2 columns in different tables.

John Furlong wrote:
>
> My question is, can the argument in AGAINST() refer to a column in
> another table or does it have to be a specific string you are
> searching for? If the MATCH() function won't work, any suggestions
> on how else to compare table1.name against table2.name? The columns
> are defined as VARCHAR.

Does anyone have an answer to this? I, too, would like to know how to
use fulltext to compare data between two columns (as opposed to
comparing data between a column and a pre-defined string). Is there any
way to do this?

Mark

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to compare 2 columns in different tables.

--0015177409884378b704739c7eb1
Content-Type: text/plain; charset=ISO-8859-1

You may be able to get what you need with three queries. Join the tables on
the column of interest. By default you'll get a list of the matches. Then
you can modify the join by adding a WHERE clause that looks for
table2.nameis null, and again by modifying the WHERE clause so it
looks for
table1.name is null.
hth,
Arthur

On Tue, Sep 15, 2009 at 6:52 AM, Mark Goodge wrote:

> John Furlong wrote:
>
>>
>> My question is, can the argument in AGAINST() refer to a column in
>> another table or does it have to be a specific string you are
>> searching for? If the MATCH() function won't work, any suggestions
>> on how else to compare table1.name against table2.name? The columns
>> are defined as VARCHAR.
>>
>
> Does anyone have an answer to this? I, too, would like to know how to use
> fulltext to compare data between two columns (as opposed to comparing data
> between a column and a pre-defined string). Is there any way to do this?
>
> Mark
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=fuller.artful@gmail.com
>
>


--
Semi-retired SQL guru, interested in interesting projects not YAFOES (yet
another order entry system).

--0015177409884378b704739c7eb1--

Re: How to compare 2 columns in different tables.

Hello Mark and John,

Mark Goodge wrote:
> John Furlong wrote:
>>
>> My question is, can the argument in AGAINST() refer to a column in
>> another table or does it have to be a specific string you are
>> searching for? If the MATCH() function won't work, any suggestions
>> on how else to compare table1.name against table2.name? The columns
>> are defined as VARCHAR.
>
> Does anyone have an answer to this? I, too, would like to know how to
> use fulltext to compare data between two columns (as opposed to
> comparing data between a column and a pre-defined string). Is there any
> way to do this?
>
> Mark
>

The manual answers this question quite clearly. From the page:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
~~~~
Full-text searching is performed using MATCH() ... AGAINST syntax.
MATCH() takes a comma-separated list that names the columns to be
searched. AGAINST takes a string to search for, and an optional modifier
that indicates what type of search to perform. The search string must be
a literal string, not a variable or a column name.
~~~~

Unfortunately, you cannot use the column name directly. However you may
be able to construct and execute a search query using the PREPARED
STATEMENT commands using the data from your other table.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-s tatements.html

--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org