I hope someone can help me with this one. For performance reasons, I have a
denormalized database. There are two tables in the database we can call
them table a and table b. Both of theses tables contain columns that are
text with comma seperated values.
example:
TABLE A
----------
id - integer - primary key
column1 - text
column3 - text
column4 - text
SAMPLE RECORDS FOR TABLE A
id: 1
column1 : vanilla, chocolate chip, black raspberry
column2 : palm handheld
column3 : oldsmobile alero, chevy corvette, toyota camery
column4 : new jersey, nevada, wyoming, texas, new mexico
id: 2
column1 : vanilla, strawberry
column2 : dell laptop, palm handheld
column3: toyota camery
column4 : ohio, alaska, hawaii
TABLE B
----------
id - integer - primary key
column1 - text
column3 - text
column4 - text
SAMPLE RECORD FOR TABLE B
id: 15
column1 : vanilla, chocolate chip, black raspberry, butter pecan
column2 : dell laptop, hp desktop, palm handheld
column3 : honda civic, chevy corvette, toyota camery
column4 : texas, new mexico, florida
What I need is to create a select statement that returns the id's for table
a that have at least one data value match in every column in table b.
The above example would return the id of 1 from table one because :
column1 matches on: vanilla and chocolate chip and black raspberry
column2 matches on: palm handheld
column3 matches on: chevy corvette and toyota camery
column4 matches on: texas and new mexico
id 2 would not be returned because there are no matches on column 4
The problem is that I am running MySQL version 3.23.58 so I can't use
subqueries or boolean match against commands.
Can anyone think of away to do this without creating a select statment that
has a million LIKE %___%
Thanks.