clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 582696 fiddles created (13295 in the last week).

CREATE TABLE Routes ( ID INT NOT NULL, ORIGIN VARCHAR(2) NOT NULL, DESTINATION VARCHAR(2) NOT NULL ); INSERT INTO Routes ( ID, ORIGIN, DESTINATION ) VALUES ( 1, 'A', 'B' ), ( 2, 'C', 'D' ), ( 3, 'B', 'A' ), ( 4, 'C', 'A' ), ( 5, 'D', 'C' ) ;
5 rows affected
 hidden batch(es)


SELECT * FROM Routes t1 LEFT JOIN Routes t2 ON t2.Origin = t1.Destination AND t2.Destination = t1.Origin;
ID ORIGIN DESTINATION ID ORIGIN DESTINATION
1 A B 3 B A
2 C D 5 D C
3 B A 1 A B
4 C A
5 D C 2 C D
 hidden batch(es)


SELECT Route, COUNT(*) as Cnt FROM (SELECT CASE WHEN (t1.Origin < t1.Destination) OR t2.Origin IS NULL THEN 'Route ' + t1.Origin + t1.Destination ELSE 'Route ' + t1.Destination + t1.Origin END as Route FROM Routes t1 LEFT JOIN Routes t2 ON t2.Origin = t1.Destination AND t2.Destination = t1.Origin ) t1 GROUP BY Route ORDER BY Route;
Route Cnt
Route AB 2
Route CA 1
Route CD 2
 hidden batch(es)