View Full Version : Searching and Cross Referencing Multiple Tables

02-19-2009, 03:30 PM

Following on from my other post; I have the full-text search working, but now need to do some advanced searches, but I'm not sure how to go about putting the query together.

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 (one > many relationship). Table2 has less fields, ID, referenceID & history, with referenceID being the field that links it to table1.

Example below

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

| 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