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???
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.