Self join tutorial on SQLZoo

Question

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?

Use a simple [SELECT DISTINCT](http://dev.mysql.com/doc/refman/5.6/en/select.html) ... — VMai, Jul 18 '14 at 23:07

score 4 · Answer 1 · answered Feb 14 '16 at 21:44

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)

(+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. — dnqxt, Nov 21 '19 at 13:16

score 2 · Answer 2 · answered Jul 18 '14 at 23:09

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.

score 2 · Accepted Answer · edited May 23 '17 at 12:24

2

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;

edited May 23 '17 at 12:24

Community

1
1

answered May 24 '15 at 17:47

DataFakeMoustache

46
2

1

You could also use ORDER BY CAST(a.num as INT) – Jay Na Feb 18 '17 at 14:53

Jun Yu · Answer 4 · 2021-12-08T01:06:02.700

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.

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. — Eduardo06sp, Dec 07 '21 at 13:47
@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. — Jun Yu, Dec 08 '21 at 01:03

Kubilay Çağlayan · Answer 5 · 2020-04-18T00:17:53.347

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

score 1 · Answer 6 · edited May 20 '21 at 16:07

At the time of posting, here is the updated question:

Find the routes involving two buses that can go from Craiglockhart to Lochend. 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.

Note that the destination changed to Lochend (147). The query below yields the correct result at the time of posting:

    SELECT DISTINCT bus1.num, bus1.company, transfer.name, bus2.num, bus2.company
     FROM route bus1 JOIN route midA ON (bus1.num = midA.num AND bus1.company = midA.company)
                     JOIN route midD ON (midA.stop = midD.stop)
                     JOIN route bus2 ON (midD.num = bus2.num AND midD.company= bus2.company)
                     JOIN stops transfer ON (midA.stop = transfer.id)
     WHERE bus1.stop = 53 AND bus2.stop = 147
     ORDER BY bus1.company, bus1.num, midA.stop, bus2.num

Note that the ORDER BY in the end reorganizes the output to fit the target result.

score 0 · Answer 7 · answered Jan 17 '16 at 07:24

Try this out, it works!

SELECT DISTINCT  a.num, a.company, 
         trans1.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 AND b.num!=c.num)
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 length(a.num), a.num

score 0 · Answer 8 · edited Feb 13 '17 at 07:11

SELECT DISTINCT sub1.num, 
                sub1.company, 
                name, 
                sub2.num, 
                sub2.company 
FROM   (SELECT r1.num, 
               r1.company, 
               r1.stop AS first, 
               r2.stop AS mid 
        FROM   route r1 
               JOIN route r2 
                 ON r1.num = r2.num 
                    AND r1.company = r2.company 
        WHERE  r1.stop = (SELECT id 
                          FROM   stops 
                          WHERE  name = 'Craiglockhart'))sub1 
       JOIN (SELECT r3.num, 
                    r3.company, 
                    r3.stop AS mid2, 
                    r4.stop AS dest 
             FROM   route r3 
                    JOIN route r4 
                      ON r3.num = r4.num 
                         AND r3.company = r4.company 
             WHERE  r4.stop = (SELECT id 
                               FROM   stops 
                               WHERE  name = 'Sighthill'))sub2 
         ON sub1.mid = sub2.mid2 
       JOIN stops 
         ON id = sub1.mid

score 0 · Answer 9 · edited Sep 09 '18 at 01:27

SELECT DISTINCT x.num,x.company,x.name,y.num,y.company 
FROM 
(
SELECT a.num as num,a.company as company,sb.name as name
FROM route a
JOIN route b
    ON a.company = b.company AND a.num = b.num
JOIN stops sa
    ON sa.id = a.stop
JOIN stops sb
    ON sb.id = b.stop
WHERE 
    sa.name = 'Craiglockhart'
) x

JOIN 

(
SELECT a.num as num, a.company as company,sb.name as name
FROM route a
JOIN route b
    ON a.company = b.company AND a.num = b.num
JOIN stops sa
    ON sa.id = a.stop
JOIN stops sb
    ON sb.id = b.stop
WHERE sa.name = 'Sighthill'
) y

ON x.name = y.name

score 0 · Answer 10 · answered Oct 19 '20 at 03:18

I use the code below and the website accepted it as a correct answer:

"one" stands for the first bus; "second" represents the second bus.

select distinct 
#one.start,
#two.end,
one.num,
one.company,
one.transfer as name, 
two.num,
two.company
from
(select distinct -- make sure you use select distinct here or else you will get an error "The SELECT would examine more than MAX_JOIN_SIZE rows”
a.num,
a.company,
stopsa.name as start,
stopsb.name as transfer,
stopsb.id as transferid
from route a 
inner join route b
on (a.num = b.num and a.company = b.company)
join stops stopsa on (stopsa.id = a.stop) 
join stops stopsb on (stopsb.id = b.stop)
where stopsa.name = 'Craiglockhart' and stopsb.name != 'Lochend' -- we don't want the first bus to lead us directly to 'Lochend'
) one

join 

(select distinct
c.num,
c.company,
stopsc.name as transfer,
stopsd.name as end
from route c
inner join route d
on c.num = d.num and c.company = d.company
join stops stopsc on stopsc.id = c.stop
join stops stopsd on stopsd.id = d.stop
where stopsd.name = 'Lochend' and stopsc.name != 'Craiglockhart' -- we don't want the second bus to go from Craiglockhart to Lochend
) two
on two.transfer = one.transfer -- first bus and second bus' overlap --> the transfer stop!
ORDER BY one.company, one.num, one.transfer, two.num -- i addded this final statement just to tailor my code to fit the correct answer. think this is a bug with sqlzoo.

score 0 · Answer 11 · edited Oct 07 '21 at 22:24

I got the correct answer by running the following query:

SELECT a.num,a.company,stopb.name,c.num,c.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 and b.num <>c.num)
    JOIN route d ON(c.company=d.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' AND stopb.name=stopc.name
    ORDER BY cast(a.num as char),stopb.name,cast(c.num as char)

score -1 · Answer 12 · edited Oct 12 '15 at 16:18

Please check a possible solution:

SELECT distinct StartOfR1.num, StartOfR1.company, Xfer.name xfer_name,  EndOfR2.num, EndOfR2.company
FROM stops Start, stops Xfer, stops Finish, route StartOfR1, route EndOfR1, route StartOfR2, route EndOfR2 
WHERE Start.name='Craiglockhart' AND Finish.name='Sighthill' AND StartOfR1.stop= Start.id -- R1 actually visits Start 
AND EndOfR1.num = StartOfR1.num  -- no transfer on the same route 
AND EndOfR1.stop= StartOfR2.stop   -- R2 starts where R1 ends 
AND EndOfR1.num != StartOfR2.num -- R1 and R2 are not the same route 
AND EndOfR1.stop = Xfer.id-- R1 changes to R2 
AND EndOfR2.company = StartOfR2.company -- R1 changes bus to R2 
AND EndOfR2.num = StartOfR2.num  -- two stops on the same route 
AND EndOfR2.stop = Finish.id -- R2 actually visits Finish;

Source.

Self join tutorial on SQLZoo

12 Answers12

Linked