Query help.

[jtown]

Ok I'm trying to write a search function and I have the following word occurence table:

WORDID | THREADID

1 | 100
3 | 100
5 | 99
8 | 100
1 | 99

Now I I have a list of WORD ID's and I need to return results that have all the WORD ID's. For instance, if I have the word ID's 1 and 3, I need to return 100, but not 99. Is this possible in a single query?

[s_shrum]

is it that you want the results to return those records where the THREADID are the same regardless of other matching WORDIDs?

If this is the case I am pretty sure that this can be done in a WHERE clause. I'm not all that up on my SQL but I've done something like this in the past. If I can find the clause text, I'll post it here.

Sorry I couldn't be of more immediate help.

Sean Shrum

[jtown]

Basically I need it to return the thread id that has all the matching word id's I'm looking for. the problem is that they're in different rows so you can't just do a where wordid=blah AND wordid=blah ANd etc.

[nnichols]

Not particularly "with it" today but I think you can do it with the following -

SELECT DISTINCT(a.THREADID)
FROM word_occurence a,word_occurence b
WHERE a.THREADID=b.THREADID
AND a.WORDID=1
AND b.WORDID=3

With a query like this it is very important that the table is efficiently indexed.

Hope this helps 😉