SQLZoo Tutorial on Self Join

Question

I 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
    -- From Craiglockhart to the transfer stop
    on (a.num=b.num and a.company=b.company)
    join route c
    -- to connect the transfer stop
    on (b.stop=c.stop)
    join route d
    -- From transfer stop to the final stop which is Sighthill
    on (c.num=d.num and c.company=d.company)
    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'
-- to delete the same route
and a.num!=c.num
order by a.num

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 I go wrong on.

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)

score 0 · Answer 3 · answered May 10 '23 at 12:38

My solution consists of two parts:

Find routes with common stop, this stop is a transfer
Filter routes: the first route should contain the stop named "Craiglockhart" in its list of stops; the second route should contain the stop named "Lochend" in its list os stops Also, I add a condition that routes should be different

SELECT r1.num, r1.company, s.name, r2.num, r2.company FROM route r1
JOIN route r2 ON r1.stop = r2.stop
JOIN stops s ON r1.stop = s.id
WHERE r1.num <> r2.num AND
(CONCAT(r1.num, r1.company) IN (SELECT CONCAT(r.num, r.company) FROM route r
JOIN stops s ON r.stop = s.id WHERE s.name = 'Craiglockhart'))
AND
(CONCAT(r2.num, r2.company) IN (SELECT CONCAT(r.num, r.company) FROM route r
JOIN stops s ON r.stop = s.id WHERE s.name = 'Lochend'))

SQLZoo Tutorial on Self Join

3 Answers3