Difference between revisions of "Self join"

SELECT COUNT(*) 
FROM stops

SELECT id 
FROM stops 
WHERE name='Craiglockhart'

SELECT id, name FROM stops, route
  WHERE id=stop
    AND company='LRT'
    AND num='4'

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2

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=149

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'

SELECT DISTINCT R1.company, R1.num
  FROM route R1, route R2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=115 AND R2.stop=137

SELECT R1.company, R1.num
  FROM route R1, route R2, stops S1, stops S2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=S1.id AND R2.stop=S2.id
    AND S1.name='Craiglockhart'
    AND S2.name='Tollcross'

SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
  WHERE S1.name='Craiglockhart'
    AND S1.id=R1.stop
    AND R1.company=R2.company AND R1.num=R2.num
    AND R2.stop=S2.id

SELECT DISTINCT a.name, c.name
 FROM stops a JOIN route z ON a.id=z.stop
 JOIN route y ON y.num = z.num
 JOIN stops b ON y.stop=b.id
 JOIN route x ON x.num = y.num
 JOIN stops c ON c.id=x.stop
 WHERE a.name='Craiglockhart'
 AND c.name ='Sighthill'