self join section from sqlzoo

sqlzooSelfJOIN.sql

/* Self join * /
/* How many stops are in the database */ 
SELECT COUNT(*)
FROM stops

/* Find the id value for the stop Craiglockhart */ 
SELECT id
FROM stops 
WHERE name = 'Craiglockhart'

/* Give the id and the name for the stops on the '4' 'LRT' service. */ 
SELECT id, name
FROM stops JOIN route ON id = stop
WHERE company = 'LRT' AND num = 4

/* Restricting output to two routes (London Road or Craiglockhart) */ 
SELECT company, num, COUNT(*)
FROM route
WHERE stop = 149 or stop = 53
GROUP BY company, num
HAVING COUNT(*) = 2

/* Execute the self join shown and observe that b.stop gives all the places
you can get to from Craiglockhart, without changing routes. Change the query
so that it shows the services from Craiglockhart to London Road. */ 
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND 
b.stop = (SELECT id FROM stops WHERE name = 'London Road')

/* The query shown is similar to the previous one, however by joining two
copies of the stops table, we can refer to stops by name rather than by number.
Change the query so that the services between Craiglockhart and London Road
are shown. If you are tired of these places, try Fairmilehead against Tollcross. */ 

SELECT a.company, a.num, stopA.name, stopB.name
FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
						  JOIN stops stopA ON (a.stop = stopA.id)
						  JOIN stops stopB ON (b.stop = stopB.id)
WHERE stopA.name = 'Craiglockhart' AND stopB.name = 'London Road'

/* Give a list of all the services which connect stops 115 and 137 */ 
SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
						  JOIN stops stopA ON (a.stop = stopA.id)
						  JOIN stops stopB ON (b.stop = stopB.id)
WHERE stopA.name = 'Haymarket' AND stopB.name = 'Leith'

/* Give a list of the service which connects the stops Craiglockhart and Tollcross */
SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
						  JOIN stops stopA ON (a.stop = stopA.id)
						  JOIN stops stopB ON (b.stop = stopB.id)
WHERE stopA.name = 'Craiglockhart' AND stopB.name = 'Tollcross' 

/* Give a distinct list of the stops which may be reached from Craiglockhart
by taking one bus, including Craiglockhart itself, offered by the LRT company.
Include the company and bus no. of the relevant services. */ 
SELECT stopA.name, a.company, a.num
FROM route a
	JOIN route b ON (a.num = b.num AND a.company = b.company)
	JOIN stops stopA ON (a.stop = stopA.id)
	JOIN stops stopB ON (b.stop = stopB.id)
WHERE stopB.name = 'Craiglockhart'