are looking for and then use having to filter it accordingly. My
example has changed your table names and column names a little just so
it would be easier for me but see if this helps:
-- see how the case is pivoting the table
SELECT EQT_SYMBOL,
HAS_TECHNOLOGY_FLAG = SUM( CASE WHEN KYW_KEYWORD = 'Technology' THEN
1 ELSE 0 END ),
HAS_CF_FLAG = SUM( CASE WHEN KYW_KEYWORD = 'CF' THEN 1 ELSE 0 END ),
HAS_FLASH_FLAG = SUM( CASE WHEN KYW_KEYWORD = 'Flash' THEN 1 ELSE 0
END ),
HAS_BILL_GATES_FLAG = SUM( CASE WHEN KYW_KEYWORD = 'Bill Gates' THEN
1 ELSE 0 END )
FROM EQUITY,
EQUITY_KEYWORD,
KEYWORD
WHERE
EQT_EQUITY_ID = EKW_EQUITY_ID
AND EKW_KEYWORD_ID = KYW_KEYWORD_ID
GROUP BY
EQT_SYMBOL
-- and this having is using the same type of case to filter
HAVING SUM( CASE WHEN KYW_KEYWORD = 'Technology' THEN 1 ELSE 0 END ) = 1
Won Lee wrote:
> I've been working on this stupid keyword DB for awhile.
> I'm not posting on the CF-Talk list because I'm no longer using CF for
> the
> client interface.
> Any insight on how to do this the most efficient way would be appreciated.
>
> I have 3 tables.
>
> equities
> --------
> equityID
> symbol
>
> keywords
> --------
> keywordID
> keyword
>
> keyword_equity_join
> ----------------------------
> keID
> equityID
> keywordID
>
> The equities and keywords have a many to many relationship.
> For example
>
> MACR (equityID 543289) - Have keywords: CF (keywordID 1), technology (2)
> and Flash(3), development (10)
> MSFT (421111)- Have keywords: technology (2), Bill Gates (4), Windows
> (5),
> development (10)
> GM (23424) - Keywords: Automotive (6), Auto Book (7), technology (2)
> PG(3211)- Keywords: Tide (8), Cleaners (9)
>
> All queries search against the keyword table and returns a symbol is the
> keyword is matched.
>
> 1) The simplest query the user can perform is a single criteria search -
> You search for 'technology' and the query should return MACR , MSFT,
> and GM.
>
> 2) The user can also perform multi criteria searches.
>
> User searches for 'technology' AND 'development'. The query yields MACR
> and MSFT only. It excludes GM because it does not have both keywords.
>
> User searches for 'flash' OR 'Tide'. The query returns MACR and PG.
>
> User searches for 'technology' NOT 'Automotive'. The query returns MACR
> and MSFT.
>
> Then the user can get crazy and search for 'technology' NOT 'auto book'
> AND 'Tide'. The query must 1) not return any stocks with 'auto book'
> then
> it must return stocks that have 'technology' and 'tide'. In this
> particular case the query returns nothing.
>
> It doesn't really follow SQL logic in that a NOT in any part of the
> search
> automatically excludes any stock related with that keyword from being
> returned.
>
> The OR part is really easy. I just add a UNION part to the query. But
> what about the 'NOT' part?
> Sorry for the rambling nature of this email.
>
> Pascal was nice enough to start me in the right direction. I have the
> app
> (written in VB.net) create the string that is my SQL.
> This is a cut and paste of what a AND query looks like.
>
> select e.symbol
> from equities as e inner join keyword_equity_join as kej0 ON e.equityID =
> kej0.equityID
> left outer join keyword_equity_join as kej1 ON e.equityID = kej1.equityID
> where e.isActive = 1 and
> kej0.keywordID = (select keywordID from keywords where keyword =
> 'technology')
> and kej1.keywordID = (select keywordID from keywords where keyword =
> 'consulting')
>
> Would be great if someone could point me towards a website that details
> keyword lookups in an abstract and obtuse sense.
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
