Self join tutorial #10 on sqlzoo

Question

I have tried http://sqlzoo.net/wiki/Self_join

Self Join Session for the #10

# 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.

Here is 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

I know the output would give you multiple rows like:

    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 you 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 the when I put DISTINCT before c.num it shows error. can't use group by since it gives you too few rows.

Can anyone experts help?

Solution 1

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;

Solution 2

My solution to this problem: I divided the problem into two.

First subquery will be the table S(Start), which will get all the routes that start from 'Craiglockhart' Second subquery will be the table E(End), which will get all the routes that start from 'Sighthill'

Now both table S and E will have common routes, and i get all this common routes by joining the subqueries, using the ids of each table. As there are duplicates routes(same: S.num, S.company, stops.name, E.num, E.company) i used DISTINCT.

SELECT DISTINCT S.num, S.company, stops.name, E.num, E.company
FROM
(SELECT a.company, a.num, b.stop
 FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
 WHERE a.stop=(SELECT id FROM stops WHERE name= 'Craiglockhart')
)S
  JOIN
(SELECT a.company, a.num, b.stop
 FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
 WHERE a.stop=(SELECT id FROM stops WHERE name= 'Sighthill')
)E
ON (S.stop = E.stop)
JOIN stops ON(stops.id = S.stop)

Solution 3

If you only want distinct rows, use the keyword DISTINCT:

SELECT DISTINCT  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 manual states:

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

Solution 4

I use the code below. a,b used for the first bus, c,d for the second bus. and b,c use the same stop to connect.

SELECT a.num, a.company, stopb.name, d.num, d.company
FROM route a JOIN route b ON (a.company = b.company AND a.num = b.num)
             JOIN route c ON (b.stop = c.stop)
             JOIN route d ON (d.company = c.company AND c.num = d.num)
             JOIN stops stopa ON a.stop = stopa.id
             JOIN stops stopb ON b.stop = stopb.id
             JOIN stops stopc ON c.stop = stopc.id
             JOIN stops stopd ON d.stop = stopd.id
WHERE stopa.name = 'Craiglockhart'
AND stopd.name = 'Lochend'
-- if you use MySQL engine, you need order by to pass the problem
order by a.num, stopb.name, d.num 

As @Eduardo06sp commented, I test this script in SQL Zoo again, SQL Zoo reports correct with Miscrosoft SQL engine, but reports wrong with MySQL engine without order by.

Solution 5

I would be happy if somebody can talk about this problem more in-depth verbally in a human way :).

There is no direct bus between the two places (Craiglockhart to Lochend in my case) And I came up with this conclusion by making another query first. (Like the 6th problem)

So I made a query for the first city and then the same for the second city and I joined them together on their matched stops. After that, all we need is to select the required columns. Here is my own answer:

SELECT 
  firstbus.busnumber AS 'num', 
  firstbus.company, 
  secondbus.transfer AS 'name', 
  secondbus.busnumber AS 'num', 
  secondbus.company 
FROM (
  SELECT r1.num AS 'busnumber', 
  r1.company AS 'company', 
  r2.stop AS 'stopp' 
  FROM route r1 
  JOIN route r2 ON (r1.num = r2.num AND r1.company = r2.company) 
  JOIN stops s1 ON s1.id = r1.stop
  JOIN stops s2 ON s2.id = r2.stop
  WHERE s1.name = 'Craiglockhart'
  ) firstbus
  JOIN
  (
  SELECT s1.name AS 'transfer', 
  r1.num AS 'busnumber', 
  r1.company AS 'company', 
  r1.stop AS 'stopp', 
  r1.pos AS 'pos' 
  FROM route r1 JOIN route r2 ON (r1.num = r2.num AND r1.company = r2.company) 
  JOIN stops s1 ON s1.id = r1.stop
  JOIN stops s2 ON s2.id = r2.stop
  WHERE s2.name = 'Lochend'
  ) secondbus
  ON firstbus.stopp = secondbus.stopp
ORDER BY firstbus.busnumber, name, 4;

With all my respect...

View more solutions

Comments

[Jay Na]

You could also use ORDER BY CAST(a.num as INT)

[dnqxt]

(+1) Just one correction: a sub-query lists all bus lines that serve Craiglockhart or Sighthill along with all the stops on these lines. The lines do not (necessarily) start at these two places.

[Eduardo06sp]

This seems correct, although SQL Zoo will report incorrect data. It seems to just be out of order. This is the cleanest answer I've seen so far.

[fish-404]

@Eduardo06sp SQL Zoo reports correct with Miscrosoft SQL engine, but reports wrong with MySQL engine. You need add order by to pass the problem if you use MySQL engine.