Hi,

>I want to query for records containing multiple subjects. But because
> the concatenated subject field can be very long, I have stored each
> subject separately within an indexed wordstore (to speed up queries).
> There is also a subject lookup table. For example, I want to return a
> record that contains both 'Queen' and 'Mary' within subjects. If I was
> just searching a concatenated subject field, then an AND would be fine.
> What's the most efficient way to return only resource records with the
> multiple subjects when querying the wordstore table?

I think you can solve this with a subquery:

SELECT
r.resource_sn,
r.concat_subjects
FROM
resources r
JOIN subjectlookup sl on sl.resource_sn = r.resource_sn
WHERE
2 = (SELECT Count(*) FROM wordstore ws WHERE
ws.wordstore_sn = sl.wordstore_sn and
ws.text IN ('Queen', 'Mary'))

Assuming a word is only once (no duplicates) stored.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info