[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