1

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

forpas
  • 160,666
  • 10
  • 38
  • 76
bonny
  • 688
  • 1
  • 14
  • 33
  • Is it important that you know which part of the query found the Distance? – RiggsFolly Apr 29 '23 at 13:54
  • @RiggsFolly Nope, not at all. I just need the answer. An answer given for this question but your answer is also welcomed. Thank You... – bonny Apr 30 '23 at 07:56

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Oh, I use `IN` operator very often but I wan not aware that I can place values in where clause and column names in place of values. Thank You so much. – bonny Apr 30 '23 at 07:54