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:
) 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?