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]
