If I have any problems I will email you on the list.
Thanks.
At 15:25 6/8/2004 -0400, you wrote:
>Actually you might be better off using max instead of sum as it gives a
>1 if true and 0 if false instead of a count of the hits where your
>having clause would have had to be HAVING MAX( CASE WHEN
>KYW_KEYWORD = 'Technology' THEN 1 ELSE 0 END ) >= 1
>
>below is a modified query with the max instead of the sum:
>
>-- SELECT *
>
>SELECT EQT_SYMBOL,
> HAS_TECHNOLOGY_FLAG = MAX( CASE WHEN KYW_KEYWORD = 'Technology' THEN
>1 ELSE 0 END ),
> HAS_CF_FLAG = MAX( CASE WHEN KYW_KEYWORD = 'CF' THEN 1 ELSE 0 END ),
> HAS_FLASH_FLAG = MAX( CASE WHEN KYW_KEYWORD = 'Flash' THEN 1 ELSE 0
>END ),
> HAS_BILL_GATES_FLAG = MAX( 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
>
>HAVING MAX( CASE WHEN KYW_KEYWORD = 'Technology' THEN 1 ELSE 0 END ) = 1
>
>
>--
>SELECT *
>FROM EQUITY,
> EQUITY_KEYWORD,
> KEYWORD
>
>WHERE
> EQT_EQUITY_ID = EKW_EQUITY_ID
>AND EKW_KEYWORD_ID = KYW_KEYWORD_ID
>
>
>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]
