MySQL Forums
Forum List  »  Newbie

Searching and Cross Referencing Multiple Tables
Posted by: Ben Blackmore
Date: February 20, 2009 04:30AM

Hi,

I want to be able to do a full-text search on 2 tables in a database, but I'd like to combine the results in a specific way. Searching the first table isn't to difficult, and I have implemented this without too much trouble. This table(1) contains an ID, a referenceID, email address, description & keywords. I'd currently search this table using full-text search against the details & keywords fields, pulling out the referenceID & email address.

Now the next table(2) holds a history for each record in table1, so table2 could have multiple records relating to a single record in table1. Table2 has less fields, ID, referenceID & history, with referenceID being the field that links it to table1.

Example below

Table1
| ID |referenceID| email | description | keywords |
|001 | XYZ123 | john@doe.com |this is a veg buyer| veg buyer|
|002 | ABC456 |joe@bloggs.com|this is a veg seller|veg seller|

Table2
| ID |referenceID| history |
|001 | XYZ123 |purchased 1 cabbage|
|002 | XYZ123 |purchased 10 carrots|
|003 | XYZ123 |purchased 20 potatoes|
|004 | ABC456 |sold 20 parsnips|
|005 | ABC456 |sold 10 carrots|

What I'd like to do, is be able to search table1.details, table1.keywords & table2.history, and display the results. But as I need to pull out an email address with each result, I need to cross reference the results from table2 with table1. I.e. If I search for 'parsnips' I need to get the referenceID ABC456 returned, from table2, along with joe@bloggs.com from table1.

What is the best way to go about this? Performing separate queries, or using a JOIN/UNION?

I'm using MySQL 5.05, with full-text indexing enabled on the relivant fields, the search page is a php form. The code I'm using the search table1 is:

"SELECT referenceID,email, MATCH(description,keywords) AGAINST('$search') AS score FROM table1 WHERE MATCH(description,keywords) AGAINST('$search' IN BOOLEAN MODE) ORDER BY score DESC";

Many thanks

Ben

Options: ReplyQuote


Subject
Written By
Posted
Searching and Cross Referencing Multiple Tables
February 20, 2009 04:30AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.