How can I do this in SQL? I need to find possible permutations of a small table of data

Question

Hi All,
I have this small table of data.

 Dir   LinkL  LinkH
 East  19   27
 East  27   29
 East  29   31
 West  46   49
 West  49   51
 West  51   61

These represent possible trips. How can I query this? For instance if you started at station 19, you could go from 19->27, 19->29, and 19->31. Three possible "trips" But starting from 27, you only have Two possible "trips", 27->29 & 27->31, lastly starting from 29 gives only one possible trip 29->31. Thats going East, with the same scenario going West you could have 3 possible "trips" starting from station 46, 46->49, 46->51, & 46->61, and so on... Making 12 trip records from the 6 inputs. How do I write a query for something like that? I started with something like this

SELECT t1.Dir, t1.LinkL FROM tblA t1 INNER JOIN tblA t2 ON t1.Dir = t2.Dir AND t2.LinkL > t1.LinkL

, but my Sql skills are somewhat limited.

The desired output would be something like this

East        19  27
East        19  29
East        19  31
East        27  29
East        27  31
East        29  31
West        46  49
West        46  51
West        46  61
West        49  51
West        49  61
West        51  61

Making for 12 possible "trips" Is there anyone out there that can point me in the right direction? I don't mind two separate queries and union the results together. In case the direction makes it more difficult. I'm also trying to avoid the dreaded cursor.

Thanks for any help.

Cheers,
~ck

Answer 1

This works:

;WITH trip_cte AS
(
    SELECT
        T1.dir,
        T1.linkl AS start,
        T1.linkh AS finish
    FROM
        dbo.Trips T1
    UNION ALL
    SELECT
        CTE.dir,
        CTE.start,
        T.linkh AS finish
    FROM
        trip_cte CTE
    INNER JOIN dbo.Trips T ON
        T.linkl = CTE.finish
)
SELECT
    dir,
    start,
    finish
FROM
    trip_cte

Answer 2

Try:

SELECT mt1.Dir, mt1.LinkL, mt2.LinkH
 from #MyTable mt1
  inner join #MyTable mt2
   on mt2.Dir = mt1.Dir
    and mt2.LinkH > mt1.LinkL
 order by mt1.Dir, mt1.LinkL, mt2.LinkH

ON clauses do not have to be based on equalities...