SQL Self Join: Why is the right half of the table different than the left half?

Question

SELECT DISTINCT a.num, a.company, a.stop,stopa.id id1, stopa.name Aname, b.num bnum,b.company bcompany ,stopb.name Bname, b.stop ,stopb.id id2
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
JOIN stops stopa ON (a.stop = stopa.id)
JOIN stops stopb ON (b.stop = stopb.id)

The original problem:

Using self-joins, find the routes involving two buses that can go from Craiglockhart to Lochend. 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.

I know that the solution is to create two tables using subqueries and then to JOIN those two together where stopids are equal. However, I don't understand why it works the way it does.

Very new to SQL and trying to understand what's going on here. I honestly need as detailed of a breakdown as you can provide.

What purpose does the route self join serve here? If stopa is the same as stopb and each is being matched to routea and routeb, why are they different in the table?

I would appreciate any assistance you can provide.

Answer 1

The self-join is needed so you can relate different stops on a route (which is what your query does) or relate different routes to each other. If you don't join, you just get one stop at a time, you're not pairing up anything.

BTW, you don't need subqueries to solve your problem. You can use a query similar to the one you have, but use a different ON condition to relate the routes. Then you'll need WHERE conditions to specify that one of the routes stops at Craiglockhart while the other stops at Lochhend. You'll need a third JOIN with stops to get the name of the transfer stop.