sqlmysqldelimitedfind-in-set

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


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.


Solution

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

    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

    Table2 - CategoryList

    Table3 - User_Category