Does this work?

select r.resource_sn, r.concat_subjects
from WordStore ws1
join SubjectLookup sl1 on (ws1.WordStore_SN = sl1.WordStore_SN)
join SubjectLookup sl2 on (sl1.Resource_SN = sl2.Resource_SN)
join wordStore ws2 on (sl2.WordStore_SN = ws2.WordStore_SN)
join Resources r on (sl1.resource_sn = r.resource_sn)
where ws1.Text = 'Queen'
and ws2.Text = 'Mary'

Adam

--- In firebird-support@yahoogroups.com, "sirmartinnz"
<sirmartinnz@y...> wrote:

> 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've tried using a Union All and then grouping by count to only show
> duplicates, but that doesn't seem to work:
>
> select r.resource_sn, r.concat_subjects from resources r
> join subjectlookup sl on sl.resource_sn = r.resource_sn
> join wordstore ws on ws.wordstore_sn = sl.wordstore_sn
> where ws.text = 'Queen'
> Union all
> select r.resource_sn, r.concat_subjects from resources r
> join subjectlookup sl on sl.resource_sn = r.resource_sn
> join wordstore ws on ws.wordstore_sn = sl.wordstore_sn
> where ws.text = 'Mary'
> group by r.resource_sn, r.concat_subjects having count(*) >1