Re: Highly obscure and erratic

On Wed, Jun 19, 2002 at 02:44:08PM +0530, Varun Kacholia wrote:
> but now returning to the main point...
> how can i execute the following query in the form above?
>
> select * from dbmedia where id IN ((select ID from wdmedia where word='word1')
> INTERSECT (select ID from wdmedia where word='word2') ...so on)

Wow, you seem to have a knack for making queries that are hard to optimise.
Do you need some kind of full-text indexing? There are premade modules that
do this all for you.

select * from dbmedia, wdmedia a, wdmedia b
where id = a.id and a.word='word1'
and   id = b.id and b.word='word2'
etc

Seems odd but it may work. But as a rule, joins are faster than subqueries
and avoid IN, INTERSECT and UNION at all costs. Do you have a book on SQL
around?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.