Checking multiple MySQL columns against a single value

Question

I have a very simple question: how can I check multiple MySQL columns against a single value? I'm looking basically for the opposite of the IN (1, 2, 3, 4) where one can check a single column against multiple values.

I know I can just write multiple sentences, but I was looking for a more elegant solution. I'm using PHP. Thank you!

Why didn't you try the opposite. The opposite works. First try then ask the question — Shiplu Mokaddim
– Shiplu Mokaddim, Commented Jan 18, 2013 at 16:18

Suresh Kamrushi · Accepted Answer · 2013-01-18 16:17:15Z

8

Use IN only like below:

select * from TableName where 2 IN (ColumnOne,ColumnTwo,ColumnThree)

answered Jan 18, 2013 at 16:17

Suresh Kamrushi

16.2k13 gold badges80 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-01-18 16:17:10Z

6

is this what you're looking for?

SELECT *
FROM   tableName
WHERE  1 IN (col1, col2, col3, col4)

answered Jan 18, 2013 at 16:17

John Woo

265k70 gold badges509 silver badges500 bronze badges

5 Comments

Mateusz Kapusta Over a year ago

Thanks, I also found out that this works: SELECT * FROM tableNAME WHERE (col1, col2) = value

Mateusz Kapusta Over a year ago

Actually there is a problem with the solution you provided. The IN statement works as an OR operand. What I need is that the conditions are met for ALL the columns (AND operand).

John Woo Over a year ago

then why not execute SELECT * FROM tableName WHERE col1 = 1 AND col2 = 1 AND col3 = 1 AND col4 = 1 ?

Mateusz Kapusta Over a year ago

I know, I was just asking if there was a more elegant solution for the problem, avoiding to get the query long (I'm creating it in a PHP script and the criteria is a quite long class property). If there is no IN-like operand that uses an AND logic instead of an OR logic I guess I have no other way.

Amol Over a year ago

I need same...but just need like on value... WHERE 'value%' IN (col1, col2, col3, col4) I want to run a big query with large data..

Pablo Santa Cruz · Accepted Answer · 2013-01-18 16:16:58Z

0

How about using or logical operator on your where clause?

select *
  from your_table
 where col_a = 'value'
    or col_b = 'value'
    or col_c = 'value'
    or col_d = 'value'

answered Jan 18, 2013 at 16:16

Pablo Santa Cruz

182k33 gold badges250 silver badges300 bronze badges

Collectives™ on Stack Overflow

Checking multiple MySQL columns against a single value

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related