replacing field values for search

twinzen

hi,

i have two tables, the first has all the information and one coloum named 'test' with numeric values, but only such numbers that are prensent in the other table.
table2 has two coloumns the first is a number and second is text.

now i would like to use fullltext search so that all data were searched.
now i'm not sure weather this is possible and if it is, then how indexes should be created, but what i want to achieve is that mysql would search as if there where according text from table2 instead of a number in table1 in coloumn 'test'.

if it were possible, then the tables should probably be linked some way but i haven't had any success so far.

thanks in advance

sfullman

Please tell me if this is correct.

Table A is the "main table" with one field called TEST. It only has numbers.

Table B is a smaller table. It has a field (we'll call it TEST also), with just numbers. There's another field called "Text" with text in it.

Am I to understand that you have UNIQUE numbers in Table A under field TEST? Where every number corresponds to a different text?

If that's true you have a one-to-many type of relationship between the two tables on the TEST field. TableA.TEST is a foreign key, and TableB.TEST is a primary key.

Here's how you'd join the tables:

Select TableA.field1, TableA.field2, TableA.field3, .. TableB.Text
FROM
TableA, TableB
WHERE
TableA.TEST = TableB.TEST and
TableB.Text like '%whatever you\'re looking for in the Text field%'

That is I THINK what you mean.

Sam Fullman
Compass Point Media

twinzen

Thank You for a quick reply!

the numbers are UNIQUE in Table B coloumn TEST.

it goes something like this

TableA

ID | TEST | OTHER1 | OTHER2 etc..
1.......1........apple ...
2.......2........pear
3.......1........plum

TableB

TEST | TEXT
..1........grape
..2........apple

i would like to achive that if i search mysql "would consider" TableA to be like:

ID | TEST | OTHER1 | OTHER2 etc..
1....grape....apple ...
2....apple......pear
3....grape.....plum

so if i ran a search for "apple", then results would be rows 1 AND 2

the TableA consists of 2000-3000 rows, TableB has some 30 rows, so i don't think there will be a lot of differnce between the speed of LIKE and MATCH.