How to query SQL rows HAVING NOT (array values) in a field with comma separated values?

I have a SQL table, which looks l开发者_如何学Goike this:

id_question (int) | tags (varchar)

where "tags" field is

- either empty : NULL

- or is filled with one value (Ex: 1) (not numeric)

- or is filled with several comma separated values (ex: 273,2308,24) (not numeric)

id_question (int) | tags (varchar)

1 | 1,373

2 | 283,4555,308,12

3 | 283,25,3

ANd i have a blacklisted_tags array. I would like to retrieve id_questions of all questions whose tags field do not have a blacklisted $tags_blacklist value.

For example:

$tags_blacklist = array (1,3)

=> I should get 2

and not 1 because it has 1 in its tags field

and not 3 because it has 3 in its tags field.

What should my SQL query look like?

---

your database design violates law of normalization #1: NEVER STORE COMMA-SEPARATED LISTS.

What you should have instead is this:

• id | tag
• 1 | 1
• 1 | 373
• 2 | 283
• 2 | 4555
• 2 | 308

etc.

This way your query becomes as easy as

SELECT DISTINCT id 
FROM YourTable
WHERE tag NOT IN (1, 3)

---

first suggestion, change your tables to the following:

question
---------
id

question_tag
------------
question_id
tag

blacklist
----------
tag