Find the routes involving two buses that can go from Craiglockhart to Sighthill. Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus.
SELECT DISTINCT
r1.num,
r1.company,
(SELECT name FROM stops WHERE id = r2.stop) AS name,
r4.num,
r4.company
FROM route AS r1
JOIN route AS r2 ON (r1.num = r2.num AND r1.company = r2.company AND r1.stop != r2.stop)
JOIN route AS r3 ON (r2.stop = r3.stop)
JOIN route AS r4 ON (r3.num = r4.num AND r3.company = r4.company)
WHERE
r1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')
AND r4.stop = (SELECT id FROM stops WHERE name = 'Sighthill') ;