yashaka yashaka - 5 months ago 10
SQL Question

Complex Subquery into Self Join from sqlzoo task 10

I am trying to solve task 10 from http://sqlzoo.net/wiki/Self_join

Here is my select in select:

SELECT DISTINCT astops.name, bbstops.name FROM
route a JOIN route b ON a.company=b.company AND a.num = b.num
JOIN stops astops ON a.stop = astops.id
JOIN stops bstops ON b.stop = bstops.id
WHERE astops.name = 'Craiglockhart' AND bstops.name IN (
SELECT aastops.name FROM
route aa JOIN route bb ON aa.company=bb.company
AND aa.num = bb.num
JOIN stops aastops ON aa.stop = aastops.id
JOIN stops bbstops ON bb.stop = bbstops.id
WHERE bbstops.name = 'Sighthill'

This sql code will not work, because I can't use table's name defined in the inner 'in' select.
The solution is to change the 'in' select subquery into self join.

Question is how to do this in this example?

I believe that the answer is close to something like this:

select astops.name, cstops.name from
route a join route b on a.company=b.company and a.num = b.num
join route c on b.company=c.company and b.num = c.num and b.stop = c.stop
join stops astops on a.stop = astops.id
join stops bstops on b.stop = bstops.id
join stops cstops on c.stop = cstops.id
where astops.name = 'Craiglockhart' and cstops.name = 'Sighthill'

But something is missed here... What is it?


Here is a more detailed version of the question:

there are two tables:
1 route(num,company, stop)  
2 stops(id, name).

So we have a table of bus routes, i.e. : bus number, its company, and a stop it pass threw... And in this select (http://pastebin.com/SQ4vcRY3) (let it be named as query1) I want to figure out is it possible to get from Craiglockhart to Sighthill by changing lanes twice.

like i am asking: Ok... I can go to somewhere from Craiglockhart... can I then get from this 'somewhere' to the 'Sighthill'?
And this query1 works! But… To make sqlzoo accept my answer… I need to show to the user not only name of the initial point (Craiglockhart) but also name of the final stop ('Sighthill')
To do this first I tried to reference final point from the subquery as:
(look http://pastebin.com/bBeLTYRL) and this did not work because you can't reference alias from the subquery.

That's why… still thinking on how to get that final stop name in the result… I decided to join subquery from query1 to the main select… As a result i got something like http://pastebin.com/JdJdV5Wm (let it be called query2)… This query2 is valid… It just does not work because shows nothing… So… I make a mistake while converting from query1 to query2…
Question is how to make this conversion properly?


SELECT DISTINCT s1.name, s2.name
FROM route r1
     JOIN stops s1 ON r1.stop=s1.id AND s1.name='Sighthill' 
     JOIN route r2 ON r1.company=r2.company AND r1.num=r2.num
     JOIN route r3 ON r2.stop=r3.stop
     JOIN route r4 ON r3.company=r4.company AND r3.num=r4.num
     JOIN stops s2 ON r4.stop=s2.id AND s2.name='Craiglockhart'