How to do a query on a column containing comma separated values

Question

so i have 2 tables

table1

    id      username      category

    1       nick          1, 2

table2

    id      category
    1        friend
    2        enemy
    3        neutral

now i want to print a list of just the categories with the id that does NOT appear in the table1 category list of coma separated values. for example in this case 'neutral'. Would appreciate the help i'm out of ideeas.

Answer 1

SELECT  b.*
FROM    table2 b 
        LEFT JOIN table1 a
            ON FIND_IN_SET(b.ID, a.category)
WHERE   a.category IS NULL

• SQLFiddle Demo

Consider normalizing your table properly. Saving comma separated value in a single column is a bad idea. Problems will arise soon if you have larger database. Here's my proposed Schema:

Table1 - UserList

• UserID (PK)
• UserName

Table2 - CategoryList

• CategoryID (PK)
• CategoryName

Table3 - User_Category

• UserID (FK) (also PK with CategoryID)
• CategoryID (FK)