Hi Alexandre, Thanks for the response.

Server did not like that one. Did 8,000,000 reads and took 3 minutes. There are only 15 records in this result set.


----- Original Message ----
From: Alexandre Benson Smith <iblist@...>
To: firebird-support@yahoogroups.com
Sent: Friday, January 19, 2007 4:06:11 PM
Subject: Re: [firebird-support] Derived Table / Embedded Select.. Help

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 KeywordKeyProductKe y Table.
> 3.) The KeywordKeyProductKe y table links to the Product Table
>
> The following SQL executes in 3.5 seconds:
>
> Select p.* From
> (Select kkpk.ProductKey From KeywordKeyProductKe y kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'LAMP%')) as FirstKeywordTable,
> (Select kkpk.ProductKey From KeywordKeyProductKe y 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 KeywordKeyProductKe y kkpk Inner Join
> Keyword kw on (kw.KeywordKey = kkpk.KeywordKey And kw.Keyword
> like 'LAMP%')) as FirstKeywordTable,
> (Select kkpk.ProductKey From KeywordKeyProductKe y 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
> keywordkeyproductke y 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
KeywordKeyProductKe y kkpk join
KeyWord kw on (kw.KeywordKey = kkpk.KeywordKey)
where
kw.Keyword like 'LAMP%' and
kkpk.ProductKey = P.ProductKey) and
exists (select
1
from
KeywordKeyProductKe y 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




[Non-text portions of this message have been removed]