Matching a value to multiple columns (in one statement) from a table using MySQL

Question

I'm working with a table in MySQL that contains the following columns:

id, january, february, march, april, etc

The data in the table looks like this:

aa, 0, 0, 1, 0
ab, 1, 0, 1, 0
ac, 1, 1, 0, 0
ad, 1, 1, 1, 0

To query it, I could easily do this:

select * from table where january = 1 and february = 1

The result would be:

ac, 1, 1, 0, 0
ad, 1, 1, 1, 0

I want to know if there's a way to do it like this:

select * from table where table.columns = 1

I want to use table columns in expression without actually specifying the names manually (typing them out).

Bonus (+1) question:
Could it be done using Match/Against like this:

select * from table
where
(
    match (somehow,get,the,table,columns,I,need,here)
    against (1 in boolean mode)
)

Thanks for your time! :)

Answer 1

You have to use a Prepared Statement, because what you want to do can only be done with dynamic SQL:

SET @stmt = 'SELECT * FROM YOUR_TABLE WHERE 1 = 1 '
SET @stmt = CONCAT(@stmt, (SELECT CONCAT_WS(' AND ', CONCAT(column_name, ' = 1 '))
                            FROM INFORMATION_SCHEMA.COLUMNS
                           WHERE table_name = 'YOUR_TABLE'
                             AND table_schema = 'db_name'
                             AND column_name NOT IN ('id'))); 

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

The first SET statement constructs a basic SELECT statement; the "1 = 1" portion is just there to make it easier to concatenate the "AND column = 1"

The second SET statement concatenates the contents of the query to get the list of columns based on the table name onto the end of the string in the first SET statement. The idea is that this:

SELECT CONCAT_WS(' AND ', CONCAT(column_name, ' = 1 '))
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'YOUR_TABLE'
   AND table_schema = 'db_name'
   AND column_name NOT IN ('id')

... will return a row that resembles "AND january = 1 AND february = 1 ...". You'd have to update the NOT IN clause if there are other columns you don't want in the WHERE clause.

The rest is just standard prepared statement stuff, and this all would have to take place within a stored procedure.

Answer 2

What you need is an un-pivot operation. Microsoft SQL Server supports PIVOT and UNPIVOT as extensions to standard SQL. I don't know of any other brand of RDBMS that supports built-in pivot/unpivot functionality. Certainly MySQL does not support this.

You can't use FULLTEXT search in this case, because as the docs say:

Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

And it wouldn't relieve you from needing to specify the columns anyway, because the MATCH() predicate needs you to list all columns in the fulltext index.

If you can't restructure the table to store a row-per-month or a single column to encode all 12 months, then you do need to generate dynamic SQL.

Answer 3

Try to create view

CREATE VIEW v_months 
AS 
SELECT *, CONCAT( CAST(jan AS CHAR(1)), 
                  CAST(feb AS CHAR(1)),
                  CAST(mar AS CHAR(1)),
                  ...) AS all 
FROM months

You will get something like this:

aa, 0, 0, 1, 0, 0010
ab, 1, 0, 1, 0, 1010
ac, 1, 1, 0, 0, 1100
ad, 1, 1, 1, 0, 1110

And then you can query

SELECT * FROM v_months WHERE all = '100110010101'

Or if you want query "get all rows, where feb = 1", you can write like this:

SELECT * FROM v_months WHERE all LIKE '_1____________'    

where '_' matches exactly one character.

Answer 4

How about solving the problem with a VIEW?

CREATE TABLE `d001ab05`.`months` (
`id` INT NOT NULL ,
`jan` BOOL NOT NULL ,
`feb` BOOL NOT NULL ,
`mar` BOOL NOT NULL ,
`apr` BOOL NOT NULL ,
`may` BOOL NOT NULL ,
`jun` BOOL NOT NULL ,
`jul` BOOL NOT NULL ,
`aug` BOOL NOT NULL ,
`sep` BOOL NOT NULL ,
`oct` BOOL NOT NULL ,
`nov` BOOL NOT NULL ,
`dec` BOOL NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

Insert data:

INSERT INTO `d001ab05`.`months` (
`id`, `jan`, `feb`, `mar`, `apr`, `may`, `jun`,
`jul`, `aug`, `sep`, `oct`, `nov`, `dec`
) VALUES (
'1', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'2', '1', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'3', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'4', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1'
);

View with Select:

CREATE OR REPLACE VIEW `moncase` AS
SELECT id,
CASE WHEN `jan` =1 OR `feb` =1 OR `mar` =1 OR `apr` =1 OR `may` =1 OR
`jun` =1 OR `jul` =1 OR `aug` =1 OR `sep` =1 OR `oct` =1 OR `nov` =1 OR `dec` =1
THEN 1 ELSE 0 END AS or_over_months
FROM months;

Select:

SELECT * FROM `moncase` WHERE `or_over_months` = 1;

Result:

id | or_over_months
1  | 1
2  | 1
4  | 1

Answer 5

You can do it using match...against.

In that case your table must be MyISAM table and you must create FULLTEXT index including required columns.

Answer 6

I understand what Mr.Smith is trying to do.

It's a question of 12 rows x 4 columns vs. 1 row x 12 columns.

The former table design would be something like:

id, someone's id, month, value x 12 per month

1, 101, january, 1
2, 101, february, 1
3, 101, march, 0
etc..

The corresponding sql statement to this would be:

$sqlQuery = "SELECT month FROM my_month_table WHERE value = 1";

What I'm guessing Mr.Smith is trying:

id, someone's id, january, february, march ...

$sqlQuery = "SELECT corrensponding_column_names_to_where_clause FROM my_month_table WHERE column_value = 1";

Answer 7

select * from table where 1 in (january, february)

Answer 8

In my opinion this is caused from what appears to be poor table design (I am guessing that is just an example to simply your problem, but bear with me, and I think this concept could be modeled better.
Here are the tables:

  Things        Things_Months      Months
   thing_id       thing_id           Month_id
   thing_info     month_id           Month_Name
                  thing_month_id     order_field

and here would be the sql:

 select thing_info, month_name
  from things, things_months, months
  where things.thing_id = things_months.thing_id
    and things_months.month_id = months.month_id 
  order by things.things_info, months.order_field

The results would be

  thingy 1, January
  thingy 1, February
  thingy 2, April
  thingy 3, November
  thingy 3, December

Answer 9

You can use vectors in MySQL:

select * from table where (january, february) = (1, 1)

Beware of different server and client collations for text columns, possibly you'll need to specify collation explicitly then.