I have tried http://sqlzoo.net/wiki/Self_join
Self Join: 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.
My 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
The output gives multiple rows:
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
Where I want:
4 LRT London Road 34 LRT
4 LRT London Road 35 LRT
4 LRT London Road 65 LRT
4 LRT London Road C5 SMT
There is also a bug that the order of a.num when I try ASC doesn't work.
Also when I put DISTINCT before c.num it shows an error.
I can't use group by since it gives too few rows.
Why?
RE: the sorting 'bug', this is due to the way the application sorts. It sorts alphabetically; so 10 comes before 2, etc. This article shows a way to do "natural sorting" using LENGTH().
For this particular problem, I was able to get the correct answer using:
ORDER BY LENGTH(a.num), b.num, trans1.id, LENGTH(c.num), d.num;