title: Self join tutorial #10 on sqlzoo
body: <p>I have tried <a href="http://sqlzoo.net/wiki/Self_join" rel="nofollow">http://sqlzoo.net/wiki/Self_join</a>    </p>
  
  <p>Self Join Session for the #10</p>
  
  <p># 10 : 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.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>SELECT   a.num, a.company, 
               trans1.name ,  c.num,  c.company
  FROM route a JOIN route b
  ON (a.company = b.company AND a.num = b.num)
  JOIN ( route c JOIN route d ON (c.company = d.company AND c.num= d.num))
  JOIN stops start ON (a.stop = start.id)
  JOIN stops trans1 ON (b.stop = trans1.id)
  JOIN stops trans2 ON (c.stop = trans2.id)
  JOIN stops end ON (d.stop =  end.id)
  WHERE  start.name = 'Craiglockhart' AND end.name = 'Sighthill'
              AND  trans1.name = trans2.name 
  ORDER BY a.num ASC , trans1.name
  </code></pre>
  
  <p>I know the output would give you multiple rows like:</p>
  
  <pre><code>    4   LRT London Road 35  LRT
      4   LRT London Road 34  LRT
      4   LRT London Road 35  LRT
      4   LRT London Road 34  LRT
      4   LRT London Road C5  SMT
  </code></pre>
  
  <p>Where you want:</p>
  
  <pre><code>    4   LRT London Road 34  LRT
      4   LRT London Road 35  LRT
      4   LRT London Road 65  LRT
      4   LRT London Road C5  SMT
  </code></pre>
  
  <p>There is also a bug that the order of <code>a.num</code> when I try <code>ASC</code> doesn't work.
  Also the when I put <code>DISTINCT</code> before <code>c.num</code> it shows error.
  can't use group by since it gives you too few rows.</p>
  
  <p>Can anyone experts help?</p>