Results 1 to 2 of 2
Thread: searching by two keywords
Dec 8, 2004, 15:33 #1
- Join Date
- Nov 2004
- St Petersburg, Russia
- 0 Post(s)
- 0 Thread(s)
searching by two keywords
I have a very common task. I have a table containing strings. I want to find strings that contain both of two words. So I should create a table containing rows with words and references to all strings that contain them. Such table will be exelent to make search by one keyword. But if I am to search by two keywords I am unsure what way to choose.
1. Create the table with pairs keyword-reference. and then:
"select c.string from keywords a, keywords b, strings c where a.word = 'word1' and b.word = 'word2' and a.refrence = c.reference" and b.refrence = c.reference"
2. Create the table where keyword enters only once and the row contains all references for the given keyword, i.e. keword-<comma-separated list of references>. After that search by first keyword:
"select b.word from keywords a, strings b where a.word = 'word1' and a.refrence = b.reference"
after that go through the results and select the strings that contain the second keyword.
I am unsure what way is better. It certain however that the more the number of keywords the better is second method.
Also, maybe somebody can suggest another method.
Dec 8, 2004, 15:46 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
use the first method to store your keywords
(any time you store a comma-separated list inside a column, you are asking for a world of hurt)
select string from strings inner join keywords on strings.reference = keywords.refrence where word in ('word1','word2') group by string having count(*) = 2