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! :)
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.
But the WHERE.. IN clause allows only 1 column.
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.
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.
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";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With