user6571534 user6571534 - 4 months ago 8
SQL Question

Reversed query SQL

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.

Answer

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 
values (1,0,0,0),(2,8,0,0),(3,8,null,8),(4,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
Comments