mysqlselectwhere-clausesql-in

How to make a Query for seaching two different data string in two different columns?


Now I had made a Table say PinDistance in MySQL with Columns PinCode1, PinCode2, Distance.
And I have a record like this.
-------------------------------------
| PinCode1 | PinCode2 | Distance |
-------------------------------------
|  400001   |  400002   |        2       |
-------------------------------------
|  400004   |  400001   |        5       |
-------------------------------------

Now I want to pass two pincodes say '400001' and '400002' and I want to Distance in response in a query such that query checks both columns and get the data.
Currently I am doing like the following
Select Distance from PinDistance where PinCode1 = '400001' and PinCode2 = '400002' - IF I get data from this then I stop here else I pass another query interchanging pincode values simultaneously.

Select Distance from PinDistance where PinCode1 = '400002' and PinCode2 = '400001'

Is there any better way to do this???


Solution

  • Use the operator IN:

    SELECT Distance 
    FROM PinDistance 
    WHERE ('400001', '400002') IN ((PinCode1, PinCode2), (PinCode2, PinCode1));
    

    If there is a case that both combinations ('400001', '400002') and ('400002', '400001') may exist in the table and you want only 1 row as the result you can add LIMIT 1 to the query.

    See the demo.