slalom91 wrote:

> All,
>
> I am stuck.. I need help constructing a query that will show me the
> following results. Using FB2, BTW.
>
> 1.) User enters 2 Keywords.
> 2.) I have a Keyword Table that links to a KeywordKeyProductKey Table.
> 3.) The KeywordKeyProductKey table links to the Product Table
>
> The following SQL executes in 3.5 seconds:
>
> Select p.* From
> (Select kkpk.ProductKey From KeywordKeyProductKey kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'LAMP%')) as FirstKeywordTable,
> (Select kkpk.ProductKey From KeywordKeyProductKey kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'FLOURESCENT%')) as SecondKeywordTable
> Inner Join Product p on (p.ProductKey = FirstKeywordTable.ProductKey
> And p.ProductKey = SecondKeywordTable.ProductKey)
>
> When I remove the join on the product table it executes in 16ms.
> That SQL looks like this:
>
> Select ProductKey From
> (Select kkpk.ProductKey From KeywordKeyProductKey kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'LAMP%')) as FirstKeywordTable,
> (Select kkpk.ProductKey From KeywordKeyProductKey kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'FLOURESCENT%')) as SecondKeywordTable
>
> What I am trying to do is to pull all productkeys from the
> keywordkeyproductkey table that have both keywords present in the
> keyword table. I'm sure I've been looking at this too long.
>
> Thanks.
>
>
>
>

Give a try to this one:

Select
p.*
From
Product P
where
exists (select
1
from
KeywordKeyProductKey kkpk join
KeyWord kw on (kw.KeywordKey = kkpk.KeywordKey)
where
kw.Keyword like 'LAMP%' and
kkpk.ProductKey = P.ProductKey) and
exists (select
1
from
KeywordKeyProductKey kkpk join
KeyWord kw on (kw.KeywordKey = kkpk.KeywordKey)
where
kw.Keyword like 'FLOURESCENT%' and
kkpk.ProductKey = P.ProductKey)


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br