CODEWITHSUNDEEP
phpmysqlsql
mercsen
mercsen

Reputation: 692

Get All Colum names where its value equals 1 on a specific row

I was just wondering whether there is a simple statement to get all columns of a row that equals a defined value.

I have a table which represent the rights a user has.

e.g.

userid | right A | right B | right C | right D
----------------------------------------------
    1  |     0   |     0   |    1    |    1
----------------------------------------------
    2  |     1   |     0   |    1    |    1
----------------------------------------------
    3  |     0   |     0   |    0    |    1

i need a query that returns me all colums of a user that has the value 1

for user 2 the result would look like

'right A', 'right C', 'right D'

at the moment i load the complete row and iterate over the result to get the column names but i would love to do this directly in my query.

According to MySQL a where clause in show columns etc. only tests the name, but not a value of a specific row.

A query to filter all values out of a result that are not 1 would also help, but i must work without knowing all the column names :-/

maybe a procedure is an option but thats just outsourceing the code from php to mysql :-/

Any ideas or complete impossible?

Thank in advance! :)

Upvotes: 2

Views: 428

Answers (1)

John Woo
John Woo

Reputation: 263703

Since, you mentioned that you are using MySQL, you can use IF on this

SELECT  CONCAT_WS(', ', IF(`right A` = 1, 'right A', NULL), 
                        IF(`right B` = 1, 'right B', NULL),
                        IF(`right C` = 1, 'right C', NULL),
                        IF(`right D` = 1, 'right D', NULL)) columnList
FROM    tableName
WHERE   userID = 2

Create a dynamic sql for 50 columns, example

SET @dbaseName = (SELECT DATABASE());  // FILL DATABASE NAME HERE
SET @tableName = 'TableName';          //      TableName
SET @userValue = 2;                    //      value of UserID

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT('IF(`',COLUMN_NAME , '` = 1, ''',COLUMN_NAME,''', NULL)')
  ) INTO @sql
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = @dbaseName AND
       COLUMN_NAME  LIKE 'right%';

SET @sql = CONCAT('SELECT CONCAT_WS('', '',', @sql, ') AS ColumnList
                   FROM    tableName
                   WHERE   userid = ', @userValue);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 3

Related Questions