Classic Mysql Connection Query example _mysql

MySQL Connection query I believe we all have some understanding, the connection query is in the database query operation often used, the following is to introduce you to the MySQL connection query

MySQL connection query: support multiple Table connection

You can repeat the connection multiple times on the same table (aliases are important when connecting to the same table more than one time)

Example 1:

Here are 2 sheets.

Teams table

Game Results Table: Result

Problem:

Draw a table: The home team, the visitors, the results, the game time

Method One: Subquery and join query mix

Step1:

Copy Code code as follows:

Select Result.id, t_name as H_name,match_time,result from teams join result on teams.t_id=result.h_id

Step2:

Copy Code code as follows:

Select Result.id, t_name as g_name from teams join result on teams.t_id=result.g_id

Get

step3: Connect the above two tables according to the match ID equal

Copy Code code as follows:

Select T1.id,h_name,g_name,result,match_time from
(select Result.id, t_name as H_name,match_time,result from teams join result on teams.t_id=result.h_id) as T1
Join
(select Result.id, t_name as g_name from teams join result on teams.t_id=result.g_id) as T2
On T1.id=t2.id;

Can be

The result is out, a little cumbersome

Method Two: Multiple connection query

Copy Code code as follows:

Select Result.id,t1.t_name as H_name, t2.t_name as G_name, result,match_time from
Join
Teams as T1 on result.h_id=t1.t_id
Join
Teams as T2 on T2.T_ID=RESULT.G_ID;

can be obtained:

Teams table to be connected 2 times so you have to have an alias.

Example 2:

The existing table subject

Ask for such a table

Name of parent column, sub column

Connection Query

You need more aliases to connect yourself.

Copy Code code as follows:

Select T1.name as p_name,t2.name as son_name from subject as T1 join subject as T2 on T1.id=t2.pid;

Can be

The above is the entire content of this article, I hope you can enjoy.