of keywordID
Dan use a loop in your sql
<cfset aKeyword = ListToArray(form.keywordID)>
<cfset max = ArrayLen(aKeyword)>
<cfquery name="qMyQuery" datasource="#request.dsn#">
SELECT e.symbol, e.equityID
FROM equities AS e
<cfloop from="1" to="#max#" index="i">
INNER JOIN keyword_equity_join as kej#i# ON e.equityID = kej#i#.equityID
</cfloop>
WHERE 0 = 0
<cfloop from="1" to="#max#" index="i">
AND kej#i#.keywordID = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#aKeyword[i]#">
</cfloop>
</cfquery>
OR
<cfquery name="qMyQuery" datasource="#request.dsn#">
SELECT e.symbol, e.equityID
FROM equities AS e
WHERE e.equityID IN (
SELECT equityID, COUNT(equityID)
FROM keyword_equity_join
WHERE keywordID IN (<cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#form.keywordID#" list="Yes">)
GROUP BY equityID
HAVING COUNT(equityID) = <cfqueryparam cfsqltype="CF_SQL_NUMERIC"
value="#ListLen(form.keywordID)#">
)
</cfquery>
(but you can't have duplicate keywordid in the last one)
Pascal
> -----Original Message-----
> From: Won Lee [mailto:[EMAIL PROTECTED]
> Sent: maandag 7 juni 2004 17:05
> To: CF-Talk
> Subject: SQL: Keyword Lookup
>
> Hello,
>
> Have 3 tables.
> equities
> --------
> equityID
> symbol
>
> keywords
> --------
> keywordID
> keyword
>
> keyword_equity_join
> ----------------------------
> keID
> equityID
> keywordID
>
>
> The user selects from a dropdown list a keyword The user may
> then add another dropdown list to select another keyword.
> The user may add as many dropdown list as he or she wants.
>
> When the user clicks on the submit button I have a list of keywords.
> If the keywords happen to be: technology, consulting.
> I want the query to return only the symbols that have both
> technology and consulting as associated keywords.
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
