fulltext search across multiple tables

[perryub]

Hi folks,

 

I am running MySQL 5.0.45 and my DB contains three tables, two of which are relevant to this query:

 

CREATE TABLE `Students` (\n  `OEN` bigint(11) NOT NULL,\n  `LastName` text NOT NULL,\n  `FirstName`
text NOT NULL,\n  `DOB` date NOT NULL,\n  `AdultOn` date NOT NULL,\n  `MedIssues` text,\n  PRIMARY KEY  
(`OEN`),\n  FULLTEXT KEY `LastName` (`LastName`),\n  FULLTEXT KEY `FirstName` (`FirstName`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `Notes` (\n  `OEN` bigint(11) NOT NULL,\n  
`IncidentNumber` bigint(20) NOT NULL,\n  `NoteID` bigint(20) NOT NULL auto_increment,\n  `Note` text NOT NULL,\n  `EnteredBy` 
text NOT NULL,\n  `Timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,\n  PRIMARY KEY  (`NoteID`),\n  FULLTEXT KEY `Note` (`Note`)\n) 
ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

 

I currently have a query that will search the Notes.note column for results:

 

 SELECT Students.OEN, Students.LastName, Students.FirstName, Notes.Note FROM Students, Notes
WHERE Students.OEN = Notes.OEN AND MATCH (Notes.Note) AGAINST ('foo') 

 

I would like to expand the search so that I get results if the keyword is found in the Names.LastName and Names.FirstName columns as well. For example, if I search for 'Gertrude', I want to get results for people named Gertrude as well any notes that contain the word 'Gertrude'.

 

I've tried various forms of adding an AND clause, with nothing but syntax errors to show for it.

 

 

Many thanks for helping someone very new to PHP/MySQL,

 

Perry

[fenway]

You can check the refman page for the relevant syntax.

[gablu]

@ fenway

could you please give us the link of the refman page.

[fenway]

See here for starters.