CODEWITHSUNDEEP
mysqlsql
Vitaly Mirsky
Vitaly Mirsky

Reputation: 77

Select by pair of fields in MySQL

I have the following table:

+-------+-------+-------------+
|column1|column2|   column3   |
+-------+-------+-------------+
|   2   |   4   |    row 1    |   < compare with this
|   4   |   3   |    row 2    | + < here`s 4
|   5   |   2   |    row 3    | + < here`s 2
|   1   |  NULL |    row 4    | - < no 4 or 2
|   5   |   6   |    row 5    | - < no 4 or 2
|  NULL |   2   |    row 6    | + < here`s 2
+-------+-------+-------------+

The problem is how to find all rows, that contain at least one searched value. For example I need to find rows like first, so I`m looking for rows with 2 or 4 in first two columns. So my output should be rows 2,3 and 6. I do not need to find NULL values.

Please advise.

Upvotes: 0

Views: 208

Answers (1)

user330315
user330315

Reputation: 

select *
from foo
where (   2 in (col1, col2)
       or 4 in (col1, col2))
and (col3 <> 'row 1')

SQL Fiddle example: http://sqlfiddle.com/#!2/7fd81/5

Alternatively to get it more dynamically:

select t.*
from foo t
join (
  select col1, col2 
  from foo 
  where col3 = 'row 1'
) r1 on r1.col1 = t.col1 
     or r1.col2 = t.col2
     or r1.col1 = t.col2
     or r1.col2 = t.col1
where col3 <> 'row 1'

SQLFiddle: http://sqlfiddle.com/#!2/7fd81/3

Upvotes: 1

Related Questions