Help needed on MySQL search query with 2 tables

[prbrowne]

Hi

 

I have two tables as follows:

 

table 'question' with fields 'patient_id', 'answer_id' and 'terms', and

 

table 'answer' with fields 'answer_id' and 'answer_path'.

 

I need to search question.terms for a keyword match and then join that match to the corresponding foreign key (answer_id) in table answer to return 'answer_path'.

 

Any ideas on this greatly appreciated!!

[ohdang888]

$result = mysql_query("SELECT `answer_id` FROM `question` WHERE `terms` LIKE '%$key_word%' ")or die(mysql_error());

$row = mysql_fetch_array($result);

 

$a_id = $row['answer_id'];

 

$result = mysql_query("SELECT `answer_path` FROM `answer` WHERE `answer_id` = '$a_id' ")or die(mysql_error());

$row = mysql_fetch_array($result);

 

echo $row['answer_path'];