Page 1 of 1

Resultset navigation

Posted: Sun Sep 09, 2007 1:43 pm
by ameque
I have two tables in MySQL that are not related. I need to have a search box yield results from both tables and be able to navigate through the results using one navigation system.

Posted: Sun Sep 09, 2007 2:34 pm
by Christopher
Use SQL's JOIN statement, as in "SELECT * FROM table1 JOIN table2 ON table1.id=table2.table1_id WHERE foo='bar'". Use the AS keyword to get rename columns as needed.

Resultset Navigation

Posted: Tue Sep 11, 2007 9:06 am
by ameque
The UNION statement requires both tables to have same number of columns. I however have more columns on one table than the other and the search is being conducted on all columns.

Posted: Tue Sep 11, 2007 9:40 am
by CoderGoblin
You haven't really given us a lot to work with.

Assuming there is some commonality between the tables (for now I assume id)

Code: Select all

SELECT * FROM table1,table2 WHERE table1.id=table2.table1_id AND table1.foo like '%search%' OR table2.bar LIKE '%search%';
The next question is obviously if the tables are not similar, why only process one result set as surely you need to work out which table the result belongs to. You could potentially use something like LEFT OUTER JOIN's to get the results, combined with looking at your database normalisation.

Resultset Navigation

Posted: Wed Sep 12, 2007 1:04 pm
by ameque
Am developing a key search on two tables that are not related. The idea is to come out with one resultset that can be navigated through using PHP code. The UNION statement requires both tables to have same number of fields. If I use the LEFT OUTER JOIN, the search takes almost forever. HELP PLEASE!