mysql not in issue

I have a select statement that I am trying to build a list of scripts as long as the users role is not in the sc开发者_如何转开发ripts.sans_role_priority field. This works great if there is only one entry into the field but once I add more than one the whole function quits working. I am sure I am overlooking something simple, just need another set of eyes on it. Any help wold be appreciated.

script:

 SELECT * 
 FROM scripts 
 WHERE active = 1 
   AND homePage='Y' 
   AND (role_priority > 40 OR role_priority = 40) 
   AND (40 not in (sans_role_priority) ) 
 ORDER BY seq ASC

data in scripts.sans_role_priority(varchar) = "30,40".

Additional testing adds this: When I switch the values in the field to "40, 30" the select works. Continuing to debug...

---

Maybe you are looking for FIND_IN_SET().

SELECT * 
 FROM scripts 
 WHERE active = 1 
   AND homePage='Y' 
   AND (role_priority > 40 OR role_priority = 40) 
   AND NOT FIND_IN_SET('40', sans_role_priority) 
 ORDER BY seq ASC

Note that having "X,Y,Z" as VARCHAR values in some fields reveals that your DB schema may be improved in order to have X, Y and Z stored as separate values in a related table.

---

 SELECT * 
 FROM scripts
 WHERE active = 1 
   AND homePage='Y' 
   AND role_priority >= 40 
   AND NOT FIND_IN_SET(40,sans_role_priority)
 ORDER BY seq ASC

See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Note that CSV in databases is just about the worst antipattern you can find.
It should be avoided at all costs because:

• You cannot use an index on a CSV field (at least not a mentally sane one);
• Joins on CSV fields are a major PITA;
• Selects on them are uber-slow;
• They violate 1NF.
• They waste storage.

Instead of using a CSV field, consider putting sans_role_priority in another table with a link back to scripts.

table script_sans_role_priority
-------------------------------
script_id integer foreign key references script(id)
srp integer
primary key (script_id, srp)

Then the renormalized select will be:

 SELECT s.* 
 FROM scripts s
 LEFT JOIN script_sans_role_priority srp 
   ON (s.id = srp.script_id AND srp.srp = 40)
 WHERE s.active = 1 
   AND s.homePage='Y' 
   AND s.role_priority >= 40 
   AND srp.script_id IS NULL
 ORDER BY seq ASC

---

SELECT * 
 FROM scripts 
 WHERE active = '1' 
   AND homePage='Y' 
   AND role_priority >= '40'
   AND sans_role_priority <> '40'
 ORDER BY seq ASC