[thelist] Basic SQL question

[Bob Haroche]

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