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
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) -----------------------------------
