SQLZOO SELF JOIN Reference Answer (with table style and solution)

First put on the data source information, and then need to understand the composition of this database, in ROUTE, a NUM and COMOANY constitute a primary key. This means that a back of the POS and STOP will be duplicated (because there may be 2 companies of the bus to take two trips very similar company, or a company within the 2 trips to the public a few stops are overlapping), this is also the use of self-connecting the main occasions (there is a one-to-many mapping)

The title is as follows:
many stops are in the database.

In order to prevent duplicate ids from being counted, we've added theDISTINCT

SELECT DISTINCT count(id)
FROM stops

the id value for the stop ‘Craiglockhart’

SELECT id
FROM stops
WHERE name LIKE "Craiglockhart"

the id and the name for the stops on the ‘4’ ‘LRT’ service.

The important thing to note here is that '4' and 4
According to the question, it should be 4 with single quotes, and when I type 4 directly, theMYSQLIt's automatically sorted. Maybe due to different data types operate differently.

# subqueries
SELECT id,name
FROM stops
WHERE id IN 
(SELECT stop
FROM route
WHERE num='4'
AND company LIKE 'LRT'
)

# or JOIN
SELECT id,name 
FROM stops s join route r
ON s.id=r.stop
WHERE r.num='4'AND r.company LIKE 'LRT'

The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

number of routes: utilizationcountfunction (math.)

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

Ideas: At first do not understand is FROM TO this meaning of how to use programming to achieve, and then refer to his answer, found a use of JOIN self-combination can be achieved after the same form of Cartesian product (one-to-many mapping)
在这里插入图片描述 All you need to do is filter by where to find the num and company that have both sites.

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

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’

Idea: This is derived from a topic above, the main difference is that you need to stop = id, and then use JOIN to connect to another table to display the name.

The first is the answer provided by ZOO, and one thing to note (because I'm wrong about TT) is that the WHERE clause should be filtered at the end after all the tables have been concatenated.

Otherwise it's the second method I use, filtering for the rows you need first, before joining the external table.

# This is the reference answer given by SQLZOO:
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'


# This is a WHERE filter first, then join the stops table:
SELECT tmp.company,tmp.num,sa.name,sb.name
FROM
(SELECT ra.company,ra.num,ra.stop as stop1,rb.stop as stop2
FROM route ra
JOIN route rb 
ON ra.num=rb.num AND ra.company=rb.company
WHERE ra.stop=53 AND rb.stop=149) tmp
JOIN stops sa
ON sa.id=tmp.stop1
JOIN stops sb
ON sb.id=tmp.stop2

Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

Idea: This previous question is very similar to him, the only thing you need to pay attention to is that, since a company a NUM may have 2 lines passing through here at the same time, it needs to be de-emphasized (DISTINCT)

SELECT DISTINCT ra.company,ra.num
FROM route ra 
JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
WHERE ra.stop=115 AND rb.stop=137

Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

Idea: This is very similar to question 6 above, so I won't go into it.

SELECT ra.company,ra.num
FROM route ra 
JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
JOIN stops sa
ON sa.id=ra.stop
JOIN stops sb
ON sb.id=rb.stop
WHERE sa.name='Craiglockhart' AND sb.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.

Idea: also first connect the desired master table, after completing the one-to-many mapping, use the WHERE filter clause to find the 2 conditions that satisfy the need.

SELECT distinct sb.name,ra.company,ra.num
FROM route ra JOIN route rb
ON ra.num=rb.num AND ra.company=rb.company
JOIN stops sa ON sa.id=ra.stop
JOIN stops sb ON sb.id=rb.stop
WHERE sa.name= 'Craiglockhart' 
AND ra.company='LRT'

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.

Ideas: this question thought half a day, his difficulty or test is that you need to ROUTE table starting point for "Craiglockhart" of all and he connected to the site to find (very much like question 5, but the end point is not LONDON LAND just), this needs to be used JOIN (for since the) join to create a table that maps one to many). The end point is Lochend Ditto.

Finally, use JOIN to find the rows in these 2 tables that have the same end point in Table 1 (BUS1) and the same start point in Table 2 (BUS2). Then splice to the STOPS table and find the NAME output.

I referenced this blog post below for this question and wrote out my understanding of his thoughts.
（/sinat_41944023/article/details/95447119?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-16&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-16）

SELECT DISTINCT bus1.num,bus1.company,name,bus2.num,bus2.company
FROM
(SELECT ra.num,ra.company,rb.stop
FROM route ra 
JOIN route rb
ON ra.num=rb.num 
AND ra.company=rb.company
AND ra.stop!=rb.stop
WHERE ra.stop = (SELECT id FROM stops WHERE name='Craiglockhart') )bus1

JOIN

(SELECT ra.num,ra.company,ra.stop
FROM route ra 
JOIN route rb
ON ra.num=rb.num 
AND ra.company=rb.company
AND ra.stop!=rb.stop
WHERE rb.stop = (SELECT id FROM stops WHERE name='Lochend') )bus2
ON bus1.stop=bus2.stop
JOIN stops st
ON bus1.stop=st.id