If you HAVE to have both jewish and college types and only one record returned, try this:
SELECT tblgroups.*, <!--- i wouldn't do this, just saving space here ;) ---> tblgrouptypes_x.groupid, tblgrouptypes_x.grouptype <!--- perhaps returning this value is misleading as it can only tell you one of the group types ---> FROM tblgroups INNER JOIN tblgrouptypes_x ON tblgrouptypes_x.groupid = tblgroups.groupid INNER JOIN tblgrouptypes ON tblgrouptypes.grouptype = tblgrouptypes_x.grouptype WHERE tblgrouptypes_x.grouptype = 'Jewish' AND EXISTS ( SELECT * FROM tblgrouptypes_x x2 INNER JOIN tblgrouptypes types2 ON types2.grouptype = x2.grouptype WHERE x2.groupId = tblgroups.groupid and types2.type = 'Colleges')) ORDER BY tblgroups.groupsortname Untested of course Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:300359 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4