SELECT BY PAIR OF FIELDS IN MYSQL

Goti Bandu

Total Post:119

Points:835
Posted by  Goti Bandu
 1199  View(s)
Ratings:
Rate this:
Hi Expert!

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.
Thanks in advance!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: Select by pair of fields in MySQL

    Hi Goti!


    Try as following

    select * from foo where (   2 in (col1, col2) or 4 in (col1, col2)) and (least(col1,col2), greatest(col1,col2)) <> (2,4)

    I hope it helpful for you.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!