MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.30


CREATE TABLE module_access (
   module_name varchar(200),
  view_only varchar(200),
  limited_access varchar(200),
  full_access varchar(200)
  );
INSERT INTO module_access VALUES
  ('accounts','user1,user2,','user3,user4,','superadmin,admin,'),
  ('sales','user1,user4,','user3,','superadmin,admin,user2,');

Records: 2  Duplicates: 0  Warnings: 0


-- this is the query with the error:
SELECT col FROM (
   SELECT "view_only" AS col, view_only AS value FROM module_access
   UNION ALL SELECT "limited_access", limited_access FROM module_access
   UNION ALL SELECT "full_access", full_access FROM module_access
) allValues
WHERE (value LIKE '%user4,%') AND (module_name = 'accounts');

Unknown column 'module_name' in 'where clause'


-- this is the (more correct) query:
SELECT col FROM (
   SELECT module_name, "view_only" AS col, view_only AS value FROM module_access
   UNION ALL SELECT module_name, "limited_access", limited_access FROM module_access
   UNION ALL SELECT module_name, "full_access", full_access FROM module_access
) allValues
WHERE (value LIKE '%user4,%') AND (module_name = 'accounts');


col


limited_access