-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2008 12:12 PM
To: CF-Talk
Subject: Re: Need query help
Joining is what databases are good at.
=======================================
Flying is what birds are good at; but why send the whole flock when one
carrier pigeon will do? If there are hundreds of group types, why on
earth would you force your database to join the type table that many
times?
Will the solution work? Yes.
Is it simple and scalable? Not necessarily.
I have already suggested a solution (which no one seems that interested
in exploring) which would meet Will's requirements. It only requires a
single join to the type table and a "group by" regardless of how many
types there are.
Here is a working (and simplified) example which runs on MS SQL server.
declare @tblgroups table
( groupid int,
groupname varchar(50))
declare @tblgrouptypes table
( groupid int,
grouptype varchar(50))
insert into @tblgroups
(groupid, groupname)
select 1, 'group 1'
union select 2, 'group 2'
union select 3, 'group 3'
union select 4, 'group 4'
union select 5, 'group 5'
insert into @tblgrouptypes
(groupid, grouptype)
select 1, 'Jewish'
union select 1, 'Brave'
union select 2, 'Jewish'
union select 2, 'College'
union select 3, 'College'
union select 3, 'Young'
union select 3, 'Old'
union select 4, 'College'
union select 4, 'Young'
union select 5, 'Jewish'
union select 5, 'Old'
union select 5, 'College'
SELECT t.groupid,
t.groupname
FROM @tblgroups t
INNER JOIN @tblgrouptypes gt ON gt.groupid = t.groupid
WHERE gt.grouptype in ('College','Jewish') -- This would be dynamic
GROUP BY t.groupid,
t.groupname
HAVING count(*) = 2 -- This would be dynamic
ORDER BY t.groupname
~Brad
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:300434
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
