Reversed query SQL

Question

The usual way of setting up the query

SELECT * from ____
  WHERE (Column1>0) and ____

is like asking "What are the records that have column1 values > 0 and column2 .....?"

However now I want to do it the opposite way,

"For this record, what are the columns that have values > 0?"

How to write a query that returns column names if the column values meet the condition, given a specified record ID? There are non-integer columns, which should be neglected.

EDIT: My table contains many columns that take only value 0 or 1. I wan to first randomize an ID by some constraints, like

SELECT id from ____
  WHERE Views>5000 and Q1=1 and Q3=1

then do the randomization, before showing and results of which columns = 1.

"Result: ID:_____. Views:____. Q1, Q3, Q6, Q14, Q38, Q45, Q56 exists.(=1)"

Now from the answers it seems that I have to check column by column, rather than using a function that automatically checks all columns, so I think the methodology will have to be the same whether I use SQL or post-processing by PHP. I was just hoping some kind of functions can do this rather than writing loops.

out of curiosity, can you share your table structure? it's a valid question, but i'm wondering why this would ever come up — But those new buttons though.., Jul 16 '16 at 07:31
This kind of problem is symptomatic of poor design. A database table is NOT a spreadsheet. — Strawberry, Jul 16 '16 at 07:32
There are many qualitative columns in my table, containing integers 0 or 1. I want to randomize an ID by some constraints, and then output which columns have value 1 in this record. — user6571534, Jul 16 '16 at 08:06
Once you know the type of SQL you want, you could generate the SQL dynamically. You could loop through the column names of the table like f.e. [this question](http://stackoverflow.com/questions/4950252/mysql-iterate-through-column-names) and build a SQL statement. Then [execute](http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html) that SQL statement. — LukStorms, Jul 16 '16 at 09:13

LukStorms · Accepted Answer · 2016-07-16T09:21:20.770

You could build a string based on CASE WHEN's or IF's for each column that should be verified if the value is bigger than 0.

For example:

CREATE TABLE test_tbl(
   id INT NOT NULL AUTO_INCREMENT,
   col1 INT,
   col2 INT,
   col3 INT,
   PRIMARY KEY ( id )
   );

insert into test_tbl (col1,col2,col3)
values 
(0,0,0),
(8,0,0),
(8,null,8),
(8,8,8);

select 
*,
TRIM(TRAILING ',' FROM 
  concat(
   if(col1 > 0,'col1,',''),
   case when col2 > 0 then 'col2,' else '' end,
   if(col3 > 0,'col3,','')
  )
) as bigger_than_0
from test_tbl
where (col1>0 or col2>0 or col3>0);

Gives:

id  col1 col2 col3  bigger_than_0
2   8    0    0     col1
3   8    null 8     col1,col3
4   8    8    8     col1,col2,col3

score 1 · Answer 2 · answered Jul 16 '16 at 07:26

There are two possibilities with MySQL that I could think of which are CASE and IF statements, e.g. below

SELECT
id, 
case
 when a.column1 > 0 then 'column1'
 else null
end AS col1,
case
 when a.column2 > 0 then 'column2'
 else null
end AS col2
FROM table_a a;

SELECT 
id, 
IF(column1 > 0, 'column 1', 'null') as col1,
IF(column2 > 0, 'column 2', 'null') as col2
FROM table_a a;

score 0 · Answer 3 · edited Jul 17 '16 at 17:45

This what you want to know :

create table TT(col1 int,col2 int,col3 int);

insert into TT values(1,0,1);
insert into TT values(4,0,5);
insert into TT values(1,0,3);
insert into TT values(1,0,0);

select distinct
   'col1' as 'Column contains values >1'
from 
    TT
where 
    col1 > 0

union

select distinct
    'col2'  as 'Column contains values >1'
from 
    TT
where 
    col2 > 0

union

select distinct
    'col3'  as 'Column contains values >1'
from 
    TT
where  
    col3 > 0;

Result :

+---------------------------+
| Column contains values >1 |
+---------------------------+
| col1                      |
| col3                      |
+---------------------------+

because col1 and col3 have values >0 and you can customize you query however you like

Reversed query SQL

3 Answers3

Linked