SQL advanced -> Graph example: Road network. Part 2

After showing basic facts about road network of Hatsfield city in Part 1, we want to ask some questions. First: What is the longest street in this piece ?

Calculate street length:
SELECT roadname, SUM(link_len) AS total_len
FROM roads
GROUP BY roadname
ORDER BY SUM(link_len) DESC;


Results:
roadnametotal_len
Mortington Avenue498
High Amplitude Avenue464
Archibald St367
......

Ok. Mortington Avenue is the longest.

Next question: Are these two streets intersects ("Archibald St" & "High Amplitude Avenue") ? In order to answer we have to find at least one junction that they share.

Look for the shared junction:
SELECT a.juncno, a.roadname, b.roadname
FROM
  (SELECT TJunc AS juncno, roadname
  FROM roads
  WHERE roadname = 'Archibald St'
   UNION
  SELECT FJunc AS juncno, roadname
  FROM roads
  WHERE roadname = 'Archibald St') AS a
  INNER JOIN
  (SELECT TJunc AS juncno, roadname
  FROM roads
  WHERE roadname = 'High Amplitude Avenue'
   UNION
  SELECT FJunc AS juncno, roadname
  FROM roads
  WHERE roadname = 'High Amplitude Avenue') AS b
ON a.juncno = b.juncno;


Results:
juncnoa.roadnameb.roadname
8Archibald StHigh Amplitude Avenue

The answer is: "Archibald St" and "High Amplitude Avenue" intersects in junction number 8.



sqlexamples.info