Self-connection of SELECT statement in simple SQL tutorial

So far, we've been connected to two different tables, so can we connect ourselves to a single table? The answer is yes.

Is it necessary to make a self connection to a table? The answer is yes.

Alias for table:

A table can be connected to itself. We need a mechanism to distinguish between two instances of a table when making a self connection.

In the FROM clause (clause) we can give this table a different alias, and then use dot (dot) to connect the alias and field name in other places where the statement needs to use the alias.

We also give two tables here to explain the self connection.

Edinburg Bus Lines,

Station table:

Stops (ID, name)

Bus Line table:

Route (NUM, company, POS, stop)

First, the Bus Line table route to connect.

SELECT * from Route R1, Route R2 WHERE R1.num=r2.num and R1.company=r2.company

We route the table with fields (num, company) for self connection. What does the result mean?

You can know any two accessible stations for each bus line.

Second, use the Stop field to route (bus Line table) from the connection.

SELECT * from Route R1, Route R2 WHERE R1.stop=r2.stop;

The result of the query is to share all the bus lines of the same station. Does this result make a lot of sense for the transfer?

From these two examples we can see that the grammatical structure of the self connection is very simple, but the semantic result is often not so easy to understand. The two tables listed here, if used properly, can solve many practical problems, such as how to transfer between any two sites.

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 '