Thanks a ton guys, I think I'll be able to muddle through it from here.
-----Original Message-----
From: rudy [mailto:[EMAIL PROTECTED]]
Sent: May 17, 2002 7:13 AM
To: [EMAIL PROTECTED]
Subject: Re: [CFTALKTor] Multiple variable comparisons.
hi jeremy
> Each person fills out the same form but they are allowed
> to make multiple selections for each criteria. For example
> if one person chose ON, NS, QU as their Provinces and
> another person chose ON, MN, BC. That would register as a match.
set up the userprov table as a separate table from user
user
23
45
67
userprov
23 ON
23 NS
23 QU
45 ON
45 MN
45 BC
67 NF
if you are looking for all matches between all users, the query gets really
ugly
so let's say you are looking for matches to user 23
select distinct userid
from user, userprov
on user.userid=userprov.userid
where userprov.prov in
(select prov
from userprov
where userid = 23)
basically this says "get me the users who have any prov in the set of provs
for user 23"
if you wanted to match all provs that each user has (i.e. a match only if
they have chosen the same exact set) then that's a different query
to combine this with other tests, simply add more conditions in the WHERE
clause (assuming they apply to the same join, which is unlikely) or use
UNION to add other queries
if the additional conditions are inclusive -- you want a match on province
AND a match on something else which requires a join to a different table --
then use UNION ALL and count the number of matched userids that this
returns (to eual the number of queries in the UNION)
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)
