OK, with a combination of ADDT and this page http://cookbooks.adobe.com/post_Create_search_query_With_optional_fiel ds-16245.html
(THANKS TO David Powers)

I have created the following query:

mysql_select_db($database_JuniorComm, $JuniorComm);

$expected = array('role' => 'text',

                  'course'         => 'text');

$query_search = "SELECT staff.*, courses.course, courses_rel.course_date, roles.role FROM ((((staff LEFT JOIN courses_rel ON courses_rel.staff_ID=staff.staff_ID) LEFT JOIN courses ON courses.course_ID=courses_rel.course_ID) LEFT JOIN roles_rel ON roles_rel.Staff_ID=staff.staff_ID) LEFT JOIN roles ON roles.roles_ID=roles_rel.roles_ID)";

// Set a flag to indicate whether the query has a WHERE clause

$where = false;

// Loop through the associatiave array of expected search values

foreach ($expected as $var => $type) {

  if (isset($_GET[$var])) {

    $value = trim(urldecode($_GET[$var]));

    if (!empty($value)) {

      // Check if the value begins with > or <

      // If so, use it as the operator, and extract the value

      if ($value[0] == '>' || $value[0] == '<') {

        $operator = $value[0];

        $value = ltrim(substr($value, 1));

      } elseif (strtolower($type) != 'like') {

        $operator = '=';

      }

      // Check if the WHERE clause has been added yet

      if ($where) {

        $query_search .= ' AND ';

      } else {

        $query_search .= ' WHERE ';

        $where = true;

      }

      // Build the SQL query using the right operator and data type

      $type = strtolower($type);

      switch($type) {

        case 'like':

          $query_search .= "`$var` LIKE " . GetSQLValueString('%' .

$value . '%', "text");

          break;

        case 'int':

        case 'double':

        case 'date':

          $query_search .= "`$var` $operator " .

GetSQLValueString($value, "$type");

          break;

        default:

        $query_search .= "`$var` = " . GetSQLValueString($value,

"$type");

      }

    }

  }

}

$search = mysql_query($query_search, $JuniorComm) or die(mysql_error());

$row_search = mysql_fetch_assoc($search);

$totalRows_search = mysql_num_rows($search);

I entered lead coach in the role column and pressed search but it also found all of the course column and shows all instances!! I need to only show the fact that Matthew Baker is a lead coach and display one line per staff member meeting the search criteria.

Hope this makes sense

Thanks in advance

Louie