Home > DeveloperSection > Forums > Select by pair of fields in MySQL
Goti Bandu

Total Post:119

Points:835
Posted on    May-18-2013 1:10 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 863  View(s)
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!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-18-2013 8:22 AM

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.


Don't want to miss updates? Please click the below button!

Follow MindStick