[thelist] Basic SQL question
Bob Haroche
spambait at onpointsolutions.com
Sat Jan 3 11:39:54 CST 2004
Hassan wrote:
> SELECT
> M.MemberName /* this should be explicit */
> FROM
> Members M
> , MembersColors MC1
> , MembersColors MC2
> WHERE
> M.MemberID = MC1.MemberID = MC2.MemberID
> AND
> MC1.MembersColors = 'blue'
> AND
> MC2.MembersColors = 'red'
>
>
> Does that work for your situation?
Yes, I think it will work from a SQL standpoint. Yet because the number of
colors selected varies with each submission, the Cold Fusion part necessary
to loop through the list of colors in order to construct the SQL might get
cumbersome. Someone sent me this solution off-list which would be easier to
construct using CFML:
select M.MemberID
, M.MemberName
, M.OtherColumns
from Members M
inner
join MembersColors MC
on M.MemberID = MC.MemberID
where MC.MemberColors in
('red','green','blue')
group
by M.MemberID
, M.MemberName
, M.OtherColumns
having count(*) = 3
In the above example, the only CFML I'd have to use would be #colors# for
the ('red','green','blue') part, and #ListLen(colors)# for the "3" part.
Thanks for your solution, though. It's an interesting use of self-joins
which I typically haven't had a need to use.
-------------
Regards,
Bob Haroche
O n P o i n t S o l u t i o n s
www.OnPointSolutions.com
More information about the thelist
mailing list