title: Complex Subquery into Self Join from sqlzoo task 10
body: <p>I am trying to solve task 10 from <a href="http://sqlzoo.net/wiki/Self_join" rel="nofollow">http://sqlzoo.net/wiki/Self_join</a></p>
  
  <p>Here is my select in select:</p>
  
  <pre><code>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'
    )
  </code></pre>
  
  <p>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.</p>
  
  <p>Question is how to do this in this example?</p>
  
  <p>I believe that the answer is close to something like this:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>But something is missed here... What is it?</p>
  
  <p>UPDATE:</p>
  
  <p>Here is a more detailed version of the question:</p>
  
  <p>there are two tables: 
  1 route(num,company, stop)  
  2 stops(id, name). </p>
  
  <p>So we have a table of bus routes, i.e. : bus number, its company, and a stop it pass threw... And in this select (<a href="http://pastebin.com/SQ4vcRY3" rel="nofollow">http://pastebin.com/SQ4vcRY3</a>) (let it be named as query1) I want to figure out is it possible to get from Craiglockhart to Sighthill by changing lanes twice.<br>
  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: <code>bbstops.name</code> (look <a href="http://pastebin.com/bBeLTYRL" rel="nofollow">http://pastebin.com/bBeLTYRL</a>) and this did not work because you can't reference alias from the subquery.</p>
  
  <p>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 <a href="http://pastebin.com/JdJdV5Wm" rel="nofollow">http://pastebin.com/JdJdV5Wm</a> (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?</p>