mysqlsqlinner-query

IN operator with Inner query SQL

I have this query 

SELECT *
FROM `posts`
WHERE `language` IN ( SELECT `possible_languages` FROM `users` WHERE user_id = ? );

In the table posts: languages can be either en or ar
In the table users: possible_languages can be either 'en', 'ar' or 'en','ar'
When the selected value is 'en','ar' the query doesn't work
I want it to be able to select Posts that are either en or ar IN ('en','ar')

Solution

  • You cannot use in with such lists. You can do this with exists:

    SELECT p.*
FROM `posts` p
WHERE exists (select 1
              from `users` u
              where u.user_id = ? and find_in_set(p.language, u.possible_languages)
             )

    This will be more efficient if you have an index on users(user_id, possible_languages).