No, that'll get one or the other - he wants ones that are both. Aliasing will work, something like this:
SELECT tblgroups.groupid, tblgroups.groupimage, tblgroups.groupname, tblgroups.groupcity, tblgroups.state, tblgroups.voicingid, tblgroups.groupcontactperson, tblgroups.country, tblgroups.region, x1.groupid as groupid1, x1.grouptype as grouptype1, x2.groupid as groupid2, x2.grouptype as grouptype2, gt1.grouptype, gt2.grouptype FROM tblgroups, tblgrouptypes gt1, tblgrouptypes gt2, tblgrouptypes_x x1, tblgrouptypes_x x2 WHERE tblgroups.groupid = x1.groupid AND x1.grouptype = tg1.grouptype, AND tblgroups.groupid = x2.groupid AND x2.grouptype = tg2.grouptype AND (x1.grouptype = 'College' AND x2.grouptype = 'Jewish') ORDER BY tblgroups.groupsortname On Tue, Mar 4, 2008 at 8:36 AM, ColdFusion <[EMAIL PROTECTED]> wrote: > 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:300356 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
