If you posted your actual table structures (SHOW CREATE TABLE xxxxxx\G) I think I could be more helpful. Right now I am just "shooting in the dark".
Shawn Green Database Administrator Unimin Corporation - Spruce Pine James <[EMAIL PROTECTED]> wrote on 04/26/2005 02:15:49 PM: > I tried that and maybe I'm doing something wrong but... > > -I have to select the same number of columns...for each UNION > -And each of the records from the union fall under the same column > headings as the first SELECT... > > I even tried to define column aliases.. > SELECT `running` as `running_blah`... > > -James > > > > At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: > >james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: > > > >> I have four different activities. Each has its own set of data that > >> I want to save. So, I made four different tables to hold the saved > >> data. Each record also has 'keywords' field (essentially this is the > >> only field that all tables have in common.) > >> > >> Later on, I want to search all the keywords in these tables...and > >> then retrieve the saved information from the four different tables. > >> > >> Question: > >> Should I just search each of the tables individually? > >> > >> > >> Or should I create another table that will hold the keywords, the > >> tablename, and the ID of the saved record in that particular > >> table...and then perform my search on this NEW table? > >> > >> Thanks. > >> -- > >> -James > >> > > > > > >I would properly index each table and UNION the results of the 4 > >searches. Have you considered creating a Full Text index for your > >keyword fields? > > > >Say your 4 tables are called: running, swimming, jumping, and walking > > > >SELECT 'running', <column list> > >FROM running > >WHERE <keywords search condition> > >UNION > >SELECT 'swimming', <column list> > >FROM swimming > >WHERE <keywords search condition> > >UNION > >SELECT 'jumping', <column list> > >FROM jumping > >WHERE <keywords search condition> > >UNION > >SELECT 'walking', <column list> > >FROM walking > >WHERE <keywords search condition>; > > > >I used the first column only to identify which table each match > >comes from. That way if you have records in each table with matching > >PK values, you know which table to go back to in order to get any > >additional information. The only problem with this type of search is > >that your <column list> columns must be compatible between each of > >the tables. If the second column is numeric in your first query then > >the second column will be coerced to numeric for each of the > >remaining 3 queries. If for some reason that fails, then the whole > >UNION fails and you get an error. > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > -- > -James Tu > ----------------------------------- > ESI Design > 111 Fifth Avenue 12th floor > New York, NY 10003 > (212) 989-3993 ext. 357 > (212) 673-4061 (fax) > -----------------------------------
