Quote
I would like to select the column names that have the value ‘1’ ...
Are you saying that you multiple columns, each of which might contain the value '1' and you want to find those columns?
Sounds to me like "flag" fields; if that's the case, then this is a poor design choice.
This is messy; pulls back too much data that you have to unpick in the client application.
create table t1
( id integer auto_increment
, flag1 tinyint
, flag2 tinyint
, flag3 tinyint
. . .
);
insert into t1 values ( 77, 0, 0, 1 ), ( 88, 0, 1, 0 ), ( 99, 1, 0, 0 );
select *
from t1
where flag1 = 1
or flag2 = 1
or flag3 = 1
order by 1 ;
+----+-------+-------+-------+
| id | flag1 | flag2 | flag3 |
+----+-------+-------+-------+
| 77 | 0 | 0 | 1 |
| 88 | 0 | 1 | 0 |
| 99 | 1 | 0 | 0 |
+----+-------+-------+-------+
This is better:
create table t2
( id integer auto_increment
, primary key ( id )
);
create table flags
( id integer auto_increment
, primary key ( id )
);
create table t2_flags
( t2_id integer not null
, flag_id integer not null
, . . .
, primary key ( t2_id, flag_id )
, foreign key t2_id references t2 ( id )
, foreign key flag_id references flags ( id )
);
insert into t2 values ( 77 ), ( 88 ), ( 99 ) ;
insert into flags values ( 1 ), ( 2 ), ( 3 ) ;
insert into t2_flags values ( 77, 3 ), ( 88, 2 ), ( 99, 1 ) ;
select t2.id as t2_id
, t2_flags.id as flag_id
from t2
inner join t2_flags
on t2_flags.t2_id = t2.id
order by 1, 2 ;
+-------+----------+
| t2_id | flags_id |
+-------+----------+
| 77 | 3 |
| 88 | 2 |
| 99 | 1 |
+-------+----------+
You can filter for specific flags or even for combinations of flags (using the "group by" and "having" clauses).
New flag? No need to change the table structure (new column); just add a row to the flags table.
Regards, Phill W.