-2
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)

SQL Output

Basic table diagram

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
alexapodacaf
  • 1
  • *why are they different in the table?* Because the columns pair values mentioned in joining expression are not unique. Check - in any row the eqiality for `company=bcompany` and `num=bnum` is observed - everything according to the condition. But joining condition does not require for another columns equiality. – Akina Mar 09 '22 at 18:12
  • *I don't understand.* Imagine that you have 2 rows of 2 columns with the values (1,1) and (1,2). You join by 1st column - and obtain 4 rows (1,1,1,1), (1,1,1,2), (1,2,1,1) and (1,2,1,2). 1st column = 3rd column - like you have asked. But 2nd and 4th columns may differ - you do not ask them to be equal too. – Akina Mar 09 '22 at 18:39
  • @Akina Thank you for your kindness and patience. What I wasn't understanding was that a self join is functionally a table with an inner join on itself and that the result is a Cartesian product. It was explained to me by a trainer at work that the table is 1:1, not a Cartesian product. And for some reason, this was not mentioned in any of the tutorials I completed. Anyway, thank you again. I hope you are well. – alexapodacaf Mar 09 '22 at 19:44
  • Please in code questions give a [mre]--cut & paste & runnable code & example input; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Mar 09 '22 at 21:01
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. PS Please clarify via edits & not comments. PS If you have an answer consider posting one. Comments are not for answers. PS [What is a self join for? (in english)](https://stackoverflow.com/a/37384306/3404097) – philipxy Mar 09 '22 at 21:04

1 Answers1

0

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612