[thelist] Basic SQL question
Hassan Schroeder
hassan at webtuitive.com
Sat Jan 3 11:16:09 CST 2004
Bob Haroche wrote:
>>But how are you storing them? As
>>+----------+--------------+
>>| MemberID | MemberColors |
>>+----------+--------------+
>>| 1 | red |
>>| 1 | blue |
>>| 2 | yellow |
>>| 2 | silver |
>>+----------+--------------+
> The submission form uses several checkboxes all with the name "color" so
> that when a user checks three boxes, Cold Fusion returns the value of the
> color checkbox as "color1, color2, color3".
>
> That's fine if I want to use IN in the SQL statement to choose members who
> are associated with ANY of those colors, but I need to choose members who
> are associated with ALL those colors.
OK, here's a self-join that you could generate based on how many
colors you needed to find (MC1, MC2, MC3, ...)
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?
--
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
More information about the thelist
mailing list