MySQL Connection Query Classic Small Example

MySQL Connection query:

Mysql connection queries support multiple table joins

You can repeat multiple connections to the same table (aliases are important when you concatenate the same table multiple times)

Example 1:

Here are 2 sheets

Teams table

Match result table: Result

Problem:

Draw a table:

Home team, away, race results, race time

Method One: (sub-query and join query blending)

Step1:

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

Step2:

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 ID of the match

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, it's a little cumbersome.

Method Two: Multiple connection queries

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

You can get:

Teams table to connect 2 times so to have aliases

Example 2:

The existing table subject

Request such a table

Name of parent column, sub-column

Connection Query

You need an alias to connect yourself.

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

Can be

For ease of practice, or to build tables and data to populate SQL, click Get Practice SQL

MySQL Connection Query Classic Small Example