MySQL Forums
Forum List  »  Newbie

Re: Selecting column names if value exists
Posted by: Phillip Ward
Date: April 13, 2015 05:43AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Selecting column names if value exists
April 13, 2015 05:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.