Join multiple tables in SQL (two or more tables can also be joined)

[Convert] To join multiple tables in SQL (more than two tables can also be joined)

From: http://shuihan.com/article.asp? Id = 155

First, it is simple to query two tables:
There are two tables,ArticleThe typeid field in the table records the column ID. The fields in the column table are the column ID and column name. The effect is to display the column name when reading the article list. I have never used inner join outer operations before, so I am helpless. In fact, some functions can be implemented only by SQL statements. Inner join can combine records in two tables as long as there are consistent values in the common fields. To display the column name, use the following SQL statement:
Select [article]. ID, [article]. content, [Topic table]. [Topic name] from [Article] inner join [Topic table] on [Topic table]. id = [article]. artype order by [arid] DESC

Multiple tables (join queries for more than two tables) are implemented:
Related Articles:

It is very useful to create a record set for multi-table join, because in some cases, we need to display the digital data type as the corresponding text name, this creates a record set for multi-table join. For example, as a Member registration system, there are five tables: member information data table Member, member identity table memberidentity, member permission table memberlevel, member category table membersort, and member marital status table wedlock. If you want to display all the Member registration information, you must connect the four tables. Otherwise, some member information may be only the data number.

In the member category table, 1 represents a common member, 2 represents a senior member, and 3 represents a lifetime member, if the member category table is not associated with the member Details table, we can only see that the registration information of a common member is 1, who knows that 1 represents an ordinary member? Therefore, we need to associate the member category table with the member detailed data table. After Association, 1 is displayed as a common member, 2 is displayed as a senior member, and 3 is displayed as a permanent member. How good is this? Similarly, the other two tables must be associated with the member data table to display the data number as the corresponding name.

I encountered this problem when I created the website background the day before yesterday. I posted a post for help on the bread Forum, crazy fans club, blue ideal, and 5d multimedia forum. I didn't get any answer, so I had to study it myself, it took two days to finally succeed. Now I will write it as a tutorial for you to share with us. I hope you can avoid detours.
In this tutorial, We will link five tables. If you want to, you can join more tables in a similar way ~

Step 1: Use the access software to create a database named member. five tables are created in the database: member information data table Member, member identity table memberidentity, member permission table memberlevel, member category table membersort, and member marital status table wedlock.

● Member information table member:
Memberid: Automatic ID, primary key (ID)
Membersort: Number (member type)
Membername: Text, member name
Password: Text (member password)
Memberlevel: Number (member permission)
Memberidentity: Number (membership)
Wedlock: Number (marital status)
Memberqq: Text (QQ number)
Memberemail: Text (member email)
Memberdate: date/time (Member registration date)

● Member identity table memberidentity:
Memberidentity: automatic number, primary key (ID)
Identityname: Text (member name)

● Member permission table memberlevel:
Memberlevel: automatic number, primary key (ID)
Levelname: Text (member permission name)

● Member category table membersort:
Membersort: automatic number, primary key (ID)
Sortname: Text (member category name)

● Wedlock
Wedlock: automatic number, primary key (ID)
Wedlockname: Text (member marital status category)

Note: after creating the five tables, you can set the desired category. For example, you can set two categories: "unpaid members" and "paid members ", the numbers are "1" and "2" respectively. If you set three options, the number of the third option is "3.
What we want to do below is to display numbers such as "1" and "2" as "unpaid members" and "paid members". Otherwise, who knows that "1" represents "unpaid members" and "2" represents "paid members "?

Step 2: Create a DSN data source and a record set
● Run the Dreamweaver MX software and create a DSN data source named connmember (you can also start other names) on the member registration information display page.

● Click "bind" on the server behavior panel to create a data set named membershow, select connmember for "connection", select Member for "table", select all columns, and select memberdate for "sorting, in descending order. Click "advanced" to modify the automatically generated Code :
The original code is:
Select *
From Member
Order by memberdate DESC

Modify the code:
Select *
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity) Inner join wedlock on member. wedlock = wedlock. wedlock
Order by memberdate DESC

After modifying the code, click "OK!
Now, you can open the record set and check that all the fields in the five tables are integrated in the membershow record set. You only need to bind the fields to the cells to be displayed. Now, all the numbers are changed to the corresponding names. For example, the membership permissions are no longer in the "1" and "2" numeric forms, it is changed to the corresponding name "unpaid member" and "paid member ". Other numbers are also displayed as text names. Are you happy?

Note:
● During the process of inputting letters, you must use the English half-width punctuation marks and leave a half-width space between words;
● When creating a data table, if a table is connected to multiple tables, the fields in this table must be of the "Number" data type, and the same fields in multiple tables must be primary keys, the data type is "auto-numbered. Otherwise, it is difficult to connect successfully.
● Code nesting quick method: for example, if you want to connect five tables, you only need to add a bracket to the Code connecting the four tables (the Front Bracket is added after the from clause, add the parentheses at the end of the Code), and then add "inner join table name X on table 1. field number = Table X. field number "code, so that you can join the data table without limit :)

Syntax format:
In fact, inner join ...... The syntax format of on can be summarized:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number) Inner join Table X on member. field number = Table X. field number
You only need to apply this format.

Example of ready-to-use format:
Although I have already understood it, to take care of beginners, I still use the Membership registration system as an example to provide some examples of existing syntax formats, you only need to modify the data table name and field name.

How to connect two data tables:
From Member inner join membersort on member. membersort = membersort. membersort
The syntax format can be summarized as follows:
From table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number

How to connect three data tables:
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. Field number = TABLE 2. Field number) Inner join table 3 on table 1. Field number = TABLE 3. Field number

Usage of connecting four data tables:
From (member inner join membersort on member. membersort = outer. Outer) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number

Usage of Connecting Five data tables:
From (member inner join membersort on member. membersort = membersort. membersort) Inner join memberlevel on member. memberlevel = memberlevel. memberlevel) Inner join memberidentity on member. memberidentity = memberidentity. memberidentity) Inner join wedlock on member. wedlock = wedlock. wedlock
The syntax format can be summarized as follows:
From (Table 1 inner join table 2 on table 1. field number = table 2. field number) Inner join table 3 on table 1. field number = table 3. field number) Inner join table 4 on member. field number = table 4. field number) Inner join table 5 on member. field number = table 5. field number

------------------------------------------------------------------------------

Inner Join Operation

Combine the records in two tables as long as there is a consistent value in the public field.
Syntax

From Table1 inner join Table2 on table1.field1 compopr table2.field2

The inner join operation can be divided into the following parts:
Description
Table1 and Table2 record the names of the combined tables.
Name of the joined field of field1 and field2. If they are not composed of numbers, these fields must be of the same data type and contain similar data, but they do not need to have the same name.
Compopr any relational comparison operator: "=," "<," ">," "<=," ">=," or "<> ."

Description

The inner join operation can be used in the from clause .. This is the most common connection type. As long as there are consistent values in the public fields of the two tables, the internal join will combine the records in the two tables.

You can use inner join to select all employees in each department from the department table and employee table. Instead, you can use left join or right join to create an outer join and select all departments (even if some employees do not exist) or all employees (even if some have not been assigned to the Department ).

If you try to join fields that contain memo or OLE object data, an error occurs.

You can join any two numeric fields of the same type. For example, you can join the autonumber and long fields because they have similar types. However, fields of the single and double types cannot be connected.

The following example shows how to join a class table and a product table in the Class Identifier Field:

Select categoryname, productname

From categories inner join Products

On categories. categoryid = products. categoryid;

In the preceding example, the class identifier is a joined field, but it is not included in the query output because it is not included in the SELECT statement. In this example, to include a join field, include the field name in the SELECT statement, categories. categoryid.

You can also use the following syntax to link Multiple on clauses in a join statement:

Select fields
From Table1 inner join Table2
On table1.field1 compopr table2.field1 and
On table1.field2 compopr table2.field2) or
On table1.field3 compopr table2.field3)];

You can also use the following syntax to nest a join statement:

Select fields
From Table1 inner join
(Table2 inner join [(] table3
[Inner join [(] tablex [inner join...)]
On table3.field3 compopr tablex. fieldx)]
On table2.field2 compopr table3.field3)
On table1.field1 compopr table2.field2;

In an inner join, left join or right join can be nested, but inner join cannot be nested in left join or right join.