Hi Jeremy.
As I see it, you could keep each of the matching criteria in the same table.
It all depends on how you are structuring your application. If for example
you are providing a form to the user to select yes or no to options that can
be matched on then the processing page for your form would use on query to
match the user's choices with those of the users in the database. For
example....
MatchTable
userid - match1 - match2 - match3 - match4 - etc.
-------------------------------------------------
super - t - f - t - t - etc.
spider - f - f - t - t - etc.
hulk - t - f - f - f - etc.
UserInfoTable
UserID - FirstName - LastName
super - Imtha - Superman
spider - Webs - linger
hulk - Greeng - Guy
Then you could simply (assuming you only need to match on at least ONE
possibility....
<cfquery...>
SELECT
mt.userid,
mt.match1,
mt.match2,
mt.match3,
mt.match4,
ui.firstname,
ui.lastname
FROM
MatchTable mt,
UserInfoTable ui
WHERE
mt.userid = ui.userid AND
(
mt.match1 = '#form.matchitem1#' OR
mt.match2 = '#form.matchitem2#' OR
mt.match3 = '#form.matchitem3#' OR
mt.match4 = '#form.matchitem4#'
)
</cfquery>
I think that should do the trick, although one of the more experienced
CF'ers may have a better solution. The matching portions of your where
statement could also be dynamically handled so that if you changed the
number of form elements to match on, you wouldn't need to change your query
- simply use a loop and #form.formfields# to get the form field names and
#evaluate(form.formfield)# to get the repsective values. If you're
interested in this, I have examples that members of the list were kind
enough to provide to me.
Cheers,
Karl
> From: Jeremy Oudit <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Fri, 17 May 2002 06:39:08 -0700
> To: [EMAIL PROTECTED]
> Subject: RE: [CFTALKTor] Multiple variable comparisons.
>
> So if I should store all of my data in separate tables, what would be the
> best way to query them or structure them?
>
> Using the province example again, would I use the provinces as column
> headings and place a true or false value for each one selected by the user
> along with their userID?
>
> How do I account for the 10 other variables that need to be true in order
> for the users to be matched up?
> Is it possible to join 10 tables efficiently?
>
>
>
> -----Original Message-----
> From: rudy [mailto:[EMAIL PROTECTED]]
> Sent: May 16, 2002 3:19 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [CFTALKTor] Multiple variable comparisons.
>
>
>> As for the ORs I'm a little lost on that too. Perhaps cases for
>> readability? Or would they be ORs in a select where a = a or
>> b = b statement that you're referring to Rudy?
>
> yes, i was referring to conditions in the WHERE clause combined with ORs
>
> i've been data modelling for about twenty-five years (actually, an
> equivalent of only about ten -- see http://rudy.ca/20020514.cfm) and i have
> always been very, very careful not to store lists inside a column, because
> that violates first normal form
>
> still, when you have (custom) tags that can compare lists, i must say that
> the case against lists is weakened somewhat
>
> if you can guarantee that the individual entries inside the list will never
> need to be joined to some other table, then i guess it's okay
>
> nevertheless, i would still design the intersection table, just to be safe
>
> rudy
>
>
> -
> You are subscribed to the CFUGToronto CFTALK ListSRV.
> This message has been posted by: "rudy" <[EMAIL PROTECTED]>
> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
> Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
> This System has been donated by Infopreneur, Inc.
> (http://www.infopreneur.net)
>
-
You are subscribed to the CFUGToronto CFTALK ListSRV.
This message has been posted by: "A. Karl Zarudny" <[EMAIL PROTECTED]>
To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/
Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/
This System has been donated by Infopreneur, Inc.
(http://www.infopreneur.net)
