Here is my problem. I have a table called dictionary The dictionary has the following columns
`wordID` int(10) unsigned NOT NULL auto_increment, `position` int(10) unsigned NOT NULL , `WORD` varchar(120) NOT NULL default '' What this actually holds is a index of words and their locations of a body of text. The position is just the word number in the paragraph. So, using this text, it would contain Position, word 1, What 2, This 3, Actually 4, Holds … 9, words … 12, locations … 19, locations … 23, word I am attempting to do a search upon this dictionary with several keywords, but the catch is, for a hit to be relevant, the words must be within a certain range from each other (lets say six positions) So I need some kind of search for keyword1 and keyword2, where keyword1.position BETWEEN keyword2.position-6 AND keyword2.position+6 and I would like it to return a set containing the positions. I would like to do this with only database operations and use as little post processing as possible. I am using mySQL 4.1.18 I have come across creating temporary tables, which look like they may lead to a solution. CREATE TEMPORARY TABLE keyword1 SELECT* FROM dictionary WHERE `WORD` LIKE 'word%'; CREATE TEMPORARY TABLE keyword2 SELECT* FROM dictionary WHERE `WORD` LIKE ‘location%’; And here is where I get lost. I guess I need to build a third table containing a column for keyword1 Position and a column for keyword 2 position where the two positions are within a the specified range of each other. **I know the following will not work, but it may help to illustrate what results I need SELECT keyword1.position, keyword2.position FROM (SELECT position FROM keyword1) UNION (SELECT position FROM keyword2) WHERE keyword1.position BETWEEN Keyword2.position-6 AND Keyword2.position+6 This should return all instances where keyword1 is within 6 position ranges of keyword2. SO using the example text and data above, it should return Keyword1.position, keyword2.position 9, 12 23,19 As you can see, I have no idea of what I am doing at this point, so any help would be much appreciated. Thank you. -- View this message in context: http://www.nabble.com/%22proximity%22-query-help.-tf3476131.html#a9702908 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
