How to get a repeated route from the give table?
Input:
| src | Dest |
|---|---|
| Pune | Delhi |
| Pune | Mumbai |
| Mumbai | Pune |
| Delhi | Jaipur |
| Jaipur | Jodhpur |
Output:
| src | Dest |
|---|---|
| Pune | Mumbai |
CodePudding user response：
As Squirrel said in the comments, please restrict yourself to one database.
This however should work in most:
SELECT t1.*
FROM YourTable t1
INNER JOIN YourTable t2
ON t1.src = t2.dest AND t1.dest = t2.src
WHERE t1.src > t1.dest;
By way of explanation, the where clause is there to ensure that you only return single records, otherwise it would return both Pune, Mumbai and Mumbai, Pune.