MySQL query to get column name where my search condition meets

October 25, 2022

I have a table in a MySQL database. I am given a value that occurs as a cell value in that table but I want to find the column name where my search condition meets. What is the most efficient way to find the column to which that value belongs?

But my search query to find the column name WHERE user4 exist and module_name = ‘accounts’. I am trying below but not working –

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');

My Sample Table Structure like below-

Table Name: module_access

module_name  |  view_only     |  limited_access  |  full_access
============================================================================
accounts     |  user1,user2,  | user3,user4,     | superadmin,admin,
----------------------------------------------------------------------------
sales        |  user1,user4,  | user3,           | superadmin,admin,user2, 
============================================================================

I refer this Find column that contains a given value in MySQL and use below query and works but it is not the complete solution I want.

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,%';

>Solution :

Your intent was that you first query would return "limited_access", but instead it returned an error ("Unknown column ‘module_name’ in ‘where clause’")

Fixing this problem:

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');

now this query returns: limited_access

see: DBFIDDLE

But, as others noted, your database design is not correct.