Re: intersection in SQL with only one table ?

[Robin Bowes]

chas wrote:
> 
> A JOIN is the logical choice here but would require 2 different tables.
> It seems silly to have 2 identical tables... and not scalable because I'm
> going to wish to check for intersections of 3 or 4 words too (in case
> somebody searches on 'very happy world' etc)

The usual way of doing this is:

select
	s1.docid
from
	search s1
	join search s2
where
	s1.docid = s2.docid
and
	s1.wordid = 49
and
	s2.wordid = 45

To cater for multiple search words, you need to build the query
dynamically and add a new search table for each word.

HTH,

R.
-- 
Robin Bowes - System Development Manager - Room 405A
E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: [phone]  Fax: [phone]