I haven't created real project tables yet.
But here are the test ones that I'm experimenting with.
CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ;
CREATE TABLE north ( north_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, north_1 varchar(255) default NULL, north_2 varchar(255) default NULL, north_3 varchar(255) default NULL, PRIMARY KEY (north_id) ) ;
CREATE TABLE south ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, south_1 varchar(255) default NULL, south_2 varchar(255) default NULL, south_3 varchar(255) default NULL, timestamp timestamp(14) NOT NULL, PRIMARY KEY (id) ) ;
CREATE TABLE west ( west_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, west_1 varchar(255) default NULL, PRIMARY KEY (west_id) );
I want to search on the keywords in all of these tables and retrieve the records from each table that fits the WHERE clause.
The question is...should I just:
(1) Make four queries and programmatically keep track of the results from each table? ...or
(2) Create another table (let's call it `keywords`) and pull out the keywords into this new table...and store an ID that exists in north,south, east, west...and also store a column that tells us which table this ID is from?...Then we do a query on this table?
I guess either way I would have to programmatically at some point fetch with four queries...
At 2:46 PM -0400 4/26/05, [EMAIL PROTECTED] wrote:
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 >>> >that your <column list> columns must be compatible between each of> >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>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) -----------------------------------
-- -James Tu ----------------------------------- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) -----------------------------------
