Will Tomlinson wrote:
> 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
A problem with your query is that you SELECT the 'grouptype' column.
This column cannot show both 'College' and 'Jewish' at the same time.
One solution is to return two rows for the single record in tblgroups that you
want.
One row will have 'College' and the second row will have 'Jewish':
---------------------------------
SELECT G.groupid, G.groupimage, G.groupname, G.groupcity, G.state,
G.voicingid, G.groupcontactperson, G.country, G.region,
X.groupid, X.grouptype, T.grouptype
FROM tblgroups G, tblgrouptypes T, tblgrouptypes_x X
WHERE G.groupid = X.groupid
AND X.grouptype = T.grouptype
AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z
WHERE G.groupid = Z.groupid
AND Z.grouptype = 'College'
)
AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z
WHERE G.groupid = Z.groupid
AND Z.grouptype = 'Jewish'
)
ORDER BY G.groupsortname
---------------------------------
Another solution is to not SELECT the 'grouptype' column(s).
This query will only return one row from tblgroups, containing only columns
from tblgroups:
---------------------------------
SELECT G.groupid, G.groupimage, G.groupname, G.groupcity, G.state,
G.voicingid, G.groupcontactperson, G.country, G.region
FROM tblgroups G
WHERE G.groupid IN
( SELECT DISTINCT D.groupid
FROM tblgroups D, tblgrouptypes_x X
WHERE D.groupid = X.groupid
AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z
WHERE D.groupid = Z.groupid
AND Z.grouptype = 'College'
)
AND EXISTS ( SELECT 1 FROM tblgrouptypes_x Z
WHERE D.groupid = Z.groupid
AND Z.grouptype = 'Jewish'
)
)
ORDER BY G.groupsortname
---------------------------------
You don't mention your database. Both these SQL statements work on SQLServer
and MySQL, but you may
need to adjust them a bit if you use something else.
Hope this helps.
-tom-
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:300477
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
