SQL zoo tutorial #10 Self Join

Question

I am a beginner of sql and tried to follow the hint by using self join twice but failed.

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. Hint:Self-join twice to find buses that visit Craiglockhart and Sighthill, then join those on matching stops.

My code:

select 
    a.num, a.company, stopsc.name, c.num, c.company 
from 
    route a 
    join route b 
    on (a.num=b.num and a.company=b.company) **From Craiglockhart to the transfer stop**
    join route c 
    on (b.stop=c.stop) **to connect the transfer stop**
    join route d 
    on (c.num=d.num and c.company=d.company) **From transfer stop to the final stop which is Sighthill**
    join stops stopsa 
    on (a.stop=stopsa.id)
    join stops stopsb 
    on (b.stop=stopsb.id)
    join stops stopsc 
    on (c.stop=stopsc.id)
    join stops stopsd 
    on (d.stop=stopsd.id)
where 
    stopsa.name='Craiglockhart' 
and stopsd.name='Sighthill' 
and a.num!=c.num **to delete the same route**
order by a.num

Please kindly tell me what is the logical mistake of my answer. I know that there is another answer on sqlselfjoin but I would like to know which step did I go wrong. Thank you so much!

Please read [How to ask a good question] (http://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). — Richard St-Cyr, Dec 29 '15 at 02:55

score 0 · Answer 1 · answered Dec 29 '15 at 03:02

0

Off the top of my head.. if that is your SQL statement verbatim, you need to remove the ** comment **'s.

answered Dec 29 '15 at 03:02

DaveTheRave

463
2
5

score 0 · Answer 2 · answered Feb 18 '17 at 16:31

and a.num!=c.num

With this code, I assume you're trying to remove any possible 1-bus trips. There are 2 major problems with this line of code.

1) You are preventing a possibly DIFFERENT company with the same bus# to be joined. (although in this question, this logical error doesn't make any difference)

2) While you did remove any possible 1-bus trip(without transfer of bus), you do not remove duplicates. (by the way in this Q, there is no possible 1-bus trip that takes you to the destination :D )

The reason there are duplicates is that some bus lines have duplicate rows. For example, if you execute below code, you will see the duplicates. (for a given bus line, more than 1 entry of the same stop)

  SELECT a.company, a.num, stopa.name, stopb.name
  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)
  WHERE stopa.name='London Road' AND a.num=35

Thus, to remove duplicates, you should write at the top of code

select distinct
a.num, a.company, stopsc.name, c.num, c.company

Lastly, there's a sorting bug on the website where num is not properly treated as an integer, thereby giving you the wrong answer. Therefore, put this code and you will get the "proper answer"

order by CAST(a.num as int)

SQL zoo tutorial #10 Self Join

2 Answers2