proximity query help.

[fenlon]

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]

Re: how to match all words

[fenlon]

Pat,
I am by no means great with sql, but i think i may be able to help you. You
just need to load up your query with or statements. As i understand your
question, you will be searching name for several keywords (x1,x2,...)  and
address for several keywrods (y1,y2...) 


SELECT * FROM clients
WHERE
name LIKE '%x1%'
OR 
name LIKE '%x2%'
AND
address LIKE '%y1%'
OR
address LIKE '%y2%'

This should return all records with names similar to x1 or x2 with address
similar to y1 or y2.



Patrick Aljord wrote:
 
 Hey all,
 I have a table 'clients' like this:
 id int(5),
 name varchar(55),
 address varchar(55)
 
 I would like to select all the records that have '%x%' and '%y%' but
 '%x%' can be in name and '%y%' can be in address. Also in my query
 there are generally more words to match (x,y,z,t etc) and I can't use
 full text search. Any what's the best way to do this?
 
 Thanx in advance
 
 Pat
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 

-- 
View this message in context: 
http://www.nabble.com/how-to-match-all-words-tf3412448.html#a9705200
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]