No, that won't return groups that are in the jewish AND college type.
I think you might need to join to the grouptypes table twice-- once for
each type you want.
I don't know your database schema, but you might be able to do grouptype
in ('jewish','college') then group by everything but group type and add
a "having count(*) > 1"
I'm a little unsure of what your query is expected to return. If a
group can have multiple types, then I assume that there is a one-to-many
relationship between groups and types, where a single group record has
one or more type records.
You are returning type in your query, so a group with 5 type records,
would be duplicated 5 times in the result set-- once for each type. Do
you want this, or do you want a single record for the group no matter
how many types they match?
~Brad
-----Original Message-----
From: ColdFusion [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 5:37 PM
To: CF-Talk
Subject: RE: Need query help
Try changing your AND statement to an OR statement:
AND (tblgrouptypes_x.grouptype = 'College' OR tblgrouptypes_x.grouptype
=
'Jewish')
-----Original Message-----
From: Will Tomlinson [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2008 5:39 PM
To: CF-Talk
Subject: Need query help
Hey,
I've got groups that can have one or more types associated with them. I
have
a linking table that I'm querying and filtering. But I'm not getting the
results I need.
Example: A group can be both 'College' and 'Jewish', as listed in the
many
linking table - tblgrouptypes_x.grouptype
In my query below, I'm looking for groups that are both 'College' and
'Jewish', but getting no results. Even though I know there is one in
there.
Seems simple enough.
SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname,
tblgroups.groupcity, tblgroups.state, tblgroups.voicingid,
tblgroups.groupcontactperson, tblgroups.country, tblgroups.region,
tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype,
tblgrouptypes.grouptype
FROM tblgroups, tblgrouptypes, tblgrouptypes_x
WHERE tblgroups.groupid = tblgrouptypes_x.groupid
AND tblgrouptypes_x.grouptype = tblgrouptypes.grouptype
AND (tblgrouptypes_x.grouptype = 'College'
AND tblgrouptypes_x.grouptype = 'Jewish')
ORDER BY tblgroups.groupsortname
And ideas would be appreciated.
Thanks,
Will
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:300355
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
