Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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! :)

like image 973
Mr. Smith Avatar asked Sep 03 '09 11:09

Mr. Smith


People also ask

How do I match two columns in MySQL?

Here's the generic SQL query to two compare columns (column1, column2) in a table (table1). mysql> select * from table1 where column1 not in (select column2 from table1); In the above query, update table1, column1 and column2 as per your requirement.

Can we use multiple columns in where clause?

But the WHERE.. IN clause allows only 1 column.

How do I find matching records in MySQL?

MySQL LIKE with Percentage % Wildcard: >> SELECT TeachName, subject FROM data. teacher WHERE subject LIKE 'C%'; Use of the percentage sign before the pattern means that the pattern will match the last location of a value.


2 Answers

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.

like image 56
OMG Ponies Avatar answered Sep 24 '22 05:09

OMG Ponies


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";
like image 25
Jason246 Avatar answered Sep 23 '22 05:09

Jason246