Use INNER JOIN syntax to connect multiple tables to create record sets
It is very useful to create record sets for multi-table joins, because in some cases, we need to display the numeric data type as the corresponding text name, which encounters the problem of creating record sets for multi-table joins. For example, as a member registration system, there are five tables, member information data table member, member identity table MemberIdentity table, member permission table MemberLevel, member category table MemberSort and member marital status table Wedlock. If you want to display all member registration information, you must connect these four tables, otherwise some member information you see may be just data numbers.
In terms of the member category table, in its data table, 1 represents an ordinary member, 2 represents a senior member, and 3 represents a lifetime member. When displaying, if the member category table is not associated with the member detailed data table, then if we are now seeing the registration information of an ordinary member, we can only see that its category is 1, and who will know that 1 represents an ordinary member? Therefore, you need to associate the member category table with the member detailed data table. After the association, 1 will be displayed as an ordinary member, 2 will be displayed as a senior member, and 3 will be displayed as a lifetime member. How good is this? Similarly, the other two tables must also be associated with the member detailed data table before the data number can be displayed as the corresponding name.
I encountered this problem when I was creating the website backend the day before yesterday. I posted a post to ask for help in the Bread Forum, Fantasy Club, Blue Ideal, and 5D Multimedia Forum, but I didn’t get an answer, so I had to study it myself. It took two days to finally succeed. I will write it as a tutorial for everyone to share, hoping that everyone will avoid detours.
This tutorial is about connecting five tables together. If you want, you can connect more tables together. The methods are similar~
Step 1: Use Access software to create a database called Member, and create five tables in it, namely: 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 data table member:
MemberID: automatic numbering, primary key (ID number)
MemberSort: Numbers (member category)
MemberName: text, member name
Password: Text (member password)
MemberLevel: Number (member permissions)
MemberIdentity: Number (membership)
Wedlock: Numbers (marital status)
MemberQQ: Text (QQ number)
MemberEmail: Text (member mailbox)
MemberDate: Date/Time (member registration date)
●MemberIdentity table MemberIdentity:
MemberIdentity: automatic numbering, primary key (ID number)
IdentityName: Text (membership name)
●MemberLevel:
MemberLevel: Automatic numbering, primary key (ID number)
LevelName: Text (member permission name)
●Member Category Table MemberSort:
MemberSort: automatic numbering, primary key (ID number)
SortName: Text (member category name)
●Member Marital Status Table Wedlock
Wedlock: automatic numbering, primary key (ID number)
WedlockName: Text (Member Marital Status Category)
Note: After the five tables are built, you can set the category you want by yourself, such as membership permissions. You can set two categories - "Unpaid Member" and "Paid Member", with the numbers "1" and "2" respectively. If you set three options, the number of the third option is of course "3".
What we have to do below is to display numbers such as "1" and "2" as "unpaid member" and "paid member". Otherwise, who would know that "1" represents "unpaid member" and "2" represents "paid member"?
Step 2: Create a DSN data source and record set
● Run Dreamweaver MX software and create a DSN data source named ConnMember (you can also give other names) on the member registration information display page.
●Click "Binding" in the server behavior panel to create a data set called MemberShow, select ConnMember in "Conn"; select Member in "Table"; select Member in "Columns", select MemberDate in "Sort", and select MemberDate in descending order. Click the "Advanced" button to modify the automatically generated code in the SQL box:
The original code is:
SELECT *
FROM Member
ORDER BY MemberDate DESC
Modify the code to:
SELECT *
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
ORDER BY MemberDate DESC
After modifying the code, click "OK" and the task is done!
Now, you can open the record set and take a look. All fields in the five tables are integrated in the MemberShow record set. You just need to bind the corresponding field to the cell that the field wants to display. Now, all the numerical numbers have become corresponding names, such as membership permissions, which are no longer in the form of "1" and "2", but have become corresponding names "unpaid member" and "paid member". Other numerical numbers have also become displayed text names. Are you very happy?
Notes:
●In the process of entering letters, be sure to use half-width punctuation in English and leave half-width spaces between words;
●When creating a data table, if a table is joined to multiple tables, the fields in this table must be a "number" data type, and the same fields in multiple tables must be primary keys and are "auto-numbered" data type. Otherwise, it will be difficult to successfully connect.
●Quick method for code nesting: For example, if you want to join five tables, just add a front and back bracket to the code that connects four tables (the first bracket is added at the back of the FROM, and the back bracket is added at the end of the code), and then continue to add the "INNER JOIN Table Name X ON Table 1. Field Number = Table X. Field Number" code after the brackets, so that you can join the data table infinitely:)
Syntax format:
In fact, the syntax format of INNER JOIN… ON can be summarized as:
FROM (((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN Table 3. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table X ON Member. Field number = Table X. Field number
You just need to apply this format.
Ready-made format examples:
Although I have already made it clearer, in order to take care of beginners, I will take this member registration system as an example to provide some ready-made syntax format examples. You just need to modify the data table name and field name in it.
Usage of joining two data tables:
FROM Member INNER JOIN MemberSort ON =
The syntax format can be summarized as:
FROM Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number
Usage of connecting three data tables:
FROM (Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =
The syntax format can be summarized as:
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 =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =
The syntax format can be summarized as:
FROM ((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN 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 =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
The syntax format can be summarized as:
FROM (((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN Table 3. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table 5. Field number
Usage of connecting six data tables: omitted, similar to the above connection method, please learn from one example and apply it to other examples:)
It is very useful to create record sets for multi-table joins, because in some cases, we need to display the numeric data type as the corresponding text name, which encounters the problem of creating record sets for multi-table joins. For example, as a member registration system, there are five tables, member information data table member, member identity table MemberIdentity table, member permission table MemberLevel, member category table MemberSort and member marital status table Wedlock. If you want to display all member registration information, you must connect these four tables, otherwise some member information you see may be just data numbers.
In terms of the member category table, in its data table, 1 represents an ordinary member, 2 represents a senior member, and 3 represents a lifetime member. When displaying, if the member category table is not associated with the member detailed data table, then if we are now seeing the registration information of an ordinary member, we can only see that its category is 1, and who will know that 1 represents an ordinary member? Therefore, you need to associate the member category table with the member detailed data table. After the association, 1 will be displayed as an ordinary member, 2 will be displayed as a senior member, and 3 will be displayed as a lifetime member. How good is this? Similarly, the other two tables must also be associated with the member detailed data table before the data number can be displayed as the corresponding name.
I encountered this problem when I was creating the website backend the day before yesterday. I posted a post to ask for help in the Bread Forum, Fantasy Club, Blue Ideal, and 5D Multimedia Forum, but I didn’t get an answer, so I had to study it myself. It took two days to finally succeed. I will write it as a tutorial for everyone to share, hoping that everyone will avoid detours.
This tutorial is about connecting five tables together. If you want, you can connect more tables together. The methods are similar~
Step 1: Use Access software to create a database called Member, and create five tables in it, namely: 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 data table member:
MemberID: automatic numbering, primary key (ID number)
MemberSort: Numbers (member category)
MemberName: text, member name
Password: Text (member password)
MemberLevel: Number (member permissions)
MemberIdentity: Number (membership)
Wedlock: Numbers (marital status)
MemberQQ: Text (QQ number)
MemberEmail: Text (member mailbox)
MemberDate: Date/Time (member registration date)
●MemberIdentity table MemberIdentity:
MemberIdentity: automatic numbering, primary key (ID number)
IdentityName: Text (membership name)
●MemberLevel:
MemberLevel: Automatic numbering, primary key (ID number)
LevelName: Text (member permission name)
●Member Category Table MemberSort:
MemberSort: automatic numbering, primary key (ID number)
SortName: Text (member category name)
●Member Marital Status Table Wedlock
Wedlock: automatic numbering, primary key (ID number)
WedlockName: Text (Member Marital Status Category)
Note: After the five tables are built, you can set the category you want by yourself, such as membership permissions. You can set two categories - "Unpaid Member" and "Paid Member", with the numbers "1" and "2" respectively. If you set three options, the number of the third option is of course "3".
What we have to do below is to display numbers such as "1" and "2" as "unpaid member" and "paid member". Otherwise, who would know that "1" represents "unpaid member" and "2" represents "paid member"?
Step 2: Create a DSN data source and record set
● Run Dreamweaver MX software and create a DSN data source named ConnMember (you can also give other names) on the member registration information display page.
●Click "Binding" in the server behavior panel to create a data set called MemberShow, select ConnMember in "Conn"; select Member in "Table"; select Member in "Columns", select MemberDate in "Sort", and select MemberDate in descending order. Click the "Advanced" button to modify the automatically generated code in the SQL box:
The original code is:
SELECT *
FROM Member
ORDER BY MemberDate DESC
Modify the code to:
SELECT *
FROM (((Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
ORDER BY MemberDate DESC
After modifying the code, click "OK" and the task is done!
Now, you can open the record set and take a look. All fields in the five tables are integrated in the MemberShow record set. You just need to bind the corresponding field to the cell that the field wants to display. Now, all the numerical numbers have become corresponding names, such as membership permissions, which are no longer in the form of "1" and "2", but have become corresponding names "unpaid member" and "paid member". Other numerical numbers have also become displayed text names. Are you very happy?
Notes:
●In the process of entering letters, be sure to use half-width punctuation in English and leave half-width spaces between words;
●When creating a data table, if a table is joined to multiple tables, the fields in this table must be a "number" data type, and the same fields in multiple tables must be primary keys and are "auto-numbered" data type. Otherwise, it will be difficult to successfully connect.
●Quick method for code nesting: For example, if you want to join five tables, just add a front and back bracket to the code that connects four tables (the first bracket is added at the back of the FROM, and the back bracket is added at the end of the code), and then continue to add the "INNER JOIN Table Name X ON Table 1. Field Number = Table X. Field Number" code after the brackets, so that you can join the data table infinitely:)
Syntax format:
In fact, the syntax format of INNER JOIN… ON can be summarized as:
FROM (((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN Table 3. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table X ON Member. Field number = Table X. Field number
You just need to apply this format.
Ready-made format examples:
Although I have already made it clearer, in order to take care of beginners, I will take this member registration system as an example to provide some ready-made syntax format examples. You just need to modify the data table name and field name in it.
Usage of joining two data tables:
FROM Member INNER JOIN MemberSort ON =
The syntax format can be summarized as:
FROM Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number
Usage of connecting three data tables:
FROM (Member INNER JOIN MemberSort ON =) INNER JOIN MemberLevel ON =
The syntax format can be summarized as:
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 =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =
The syntax format can be summarized as:
FROM ((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN 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 =) INNER JOIN MemberLevel ON =) INNER JOIN MemberIdentity ON =) INNER JOIN Wedlock ON =
The syntax format can be summarized as:
FROM (((Table 1 INNER JOIN Table 2 ON Table 1. Field number = Table 2. Field number) INNER JOIN Table 3. Field number) INNER JOIN Table 4. Field number) INNER JOIN Table 5. Field number
Usage of connecting six data tables: omitted, similar to the above connection method, please learn from one example and apply it to other examples:)