Join multiple table creation record sets using INNERJOIN syntax

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, it is very useful to connect these four tables to multiple tables to create a record set. In some cases, we need to display the numeric data type as the corresponding text name, which leads to the problem of creating a record set through 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.

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 in the SQL box:
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