MySQL SELECT GROUP_CONCAT and use it in a WHERE clause in another select

Question

I have two tables. One that contains account settings and one temporary options.

The settings table

--------------------------
id   account    category
--------------------------
1    account 1   main
2    account 2   main
3    account 3   general
4    account 4   main
--------------------------

Then I have options

--------------------------
id   account    option
--------------------------
1    account 1   select
2    account 2   none
--------------------------

What I want to achieve is to have only the accounts which category is main and option is not select but also exists in the first table.

so in the example data above the result should be account 2 and account 4

I have tried to GROUP_CONCAT the accounts from table accounts and then to check if any of the accounts are in the concated list.

But didn't work. It says Unknown column ConAccounts Maybe I should use WHERE EXISTS? Sorry I am not very good this :)

Answer 1

Use NOT EXISTS:

SELECT GROUP_CONCAT(s.account) 
FROM settings s
WHERE s.category = 'main'
  AND NOT EXISTS (
    SELECT 1 FROM options o
    WHERE o.account = s.account AND o.option = 'select'
  )