Using SELECT syntax 5 in ADO
Author：Eve Cole
Update Time：2009-05-30 19:54:26
Join
JOIN connections combine field records in two tables, including three types:
INNER JOIN operation formula: join and combine field records in two tables.
LEFT JOIN operation formula: joins and combines field records in two tables, and will include all records in the left table of LEFT JOIN.
RIGHT JOIN operation formula: joins and combines field records in two tables, and will include all records in the table on the right side of RIGHT JOIN.
INNER JOIN sets the operation expression related to two tables to connect and combine the field records in the two tables.
The INNER JOIN syntax is as follows:
FROM Table 1 INNER JOIN Table 2 ON Table 1. Field 1 Comparison operator Table 2. Field 2
The fields used to connect two tables, such as [Table 1. Field 1 = Table 2. Field 2], must have the same field type, but the field names do not need to be the same.
For example, the AutoNumber field type can be connected to the Long field type, but the Single Integer field type cannot be connected to the Double Integer field type.
Comparison operators can be =, <, >, <=, >=, or <>.
Fields connected by JOIN cannot contain MEMO field types or OLE object types, otherwise an error will occur.
In a JOIN expression, multiple ON clauses can be connected:
SELECT fields
FROM table 1 INNER JOIN table 2
ON Table 1. Field 1 Comparison operator table 2. Field 1 AND
ON Table 1. Field 2 Comparison operator table 2. Field 2) OR
ON table 1. field 3 comparison operator table 2. field 3)
JOIN expressions can be nested:
SELECT fields
FROM TABLE 1 INNER JOIN
(Table 2 INNER JOIN [( ]Table 3
[INNER JOIN [( ] table x [INNER JOIN ...)]
ON Table 3. Field 3 Comparison operator table x. Field x)]
ON Table 2. Field 2 Comparison operator table 3. Field 3)
ON Table 1. Field 1 Comparison operator table 2. Field 2
In an INNER JOIN, nested LEFT JOIN or RIGHT JOIN can be included, but in a LEFT JOIN or RIGHT JOIN, nested INNER JOIN cannot be included.
Let's look at an example of using this SQL command in an ASP program.
You can use Inner Join...On to combine the records in the two tables. For example, the ASP program rs26.asp is as follows, [Select Sales. Code, Product. Name, Product. Price, Sales. Quantity from Sales Inner Join Product On Product. Code = Sales.Code], use Inner Join...On to set the calculation formula related to the two tables, and combine the records in the two tables:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select Sales.Code, Product.Name, Product.Price, Sales.Quantity from SalesInner Join ProductOn Product.Code = Sales.Code"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Code</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Price</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Quantity</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
</TR>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
The above ASP program rs26.asp uses a browser on the client side to browse the execution results and display the records in the combined two tables. Since there are no name fields and price fields in the sales table, [Inner Join Product On Product. Code = Sales.Code] Find the data in the name field and price field of the product table.
The above example uses the syntax of Inner Join:
Select Sales.Code, Product.Name, Product.Price, Sales.Quantity from SalesInner Join ProductOn Product.Code = Sales.Code
The result is the same as the following using Where:
Select Sales.Code, Product.Name, Product.Price, Sales.Quantity from Sales, ProductWhere Product.Code = Sales.Code
LEFT JOIN/RIGHT JOIN
INNER JOIN connection combines related field records in two tables, which is an internal connection. You can also use:
Use the LEFT JOIN operation: to create a left outer join. In addition to obtaining the related field records in the two tables, LEFT JOIN will also include all records in the left table of LEFT JOIN, regardless of whether there are matching records in the right table.
Use the RIGHT JOIN operation: Create a right outer connection. In addition to obtaining the related field records in the two tables, RIGHT JOIN will also include all records in the right table of RIGHT JOIN, regardless of whether there are matching records in the left table.
For example, [Sales LEFT JOIN Products] can select all sales records. [Sell RIGHT JOIN Products] All product records can be selected.
For example, using Left Join...On not only obtains the related field records in the two tables, but also includes all the records in the left table of LEFT JOIN. The ASP program rs26.asp is as follows, [Select sales.code, product.name, product .Price, Sales.Quantity from SalesLeft Join ProductOn Product.Code = Sales.Code], using Left Join includes all records in the sales table on the left side of LEFT JOIN:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
'conn1.Open "driver={SQL Server};server=(Local);uid=sa;pwd=;database=NtopSamp"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select Sales.Code, Product.Name, Product.Price, Sales.Quantity from SalesLeft Join ProductOn Product.Code = Sales.Code"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Code</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Price</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Quantity</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
For the above ASP program rs26.asp, use the browser on the client side to browse the execution results. The display contains all the records in the sales table on the left side of LEFT JOIN. Since there is no name field and price field in the sales table, the sales record with code number 4 , through [Sales Left Join Product On Product.Code = Sales.Code], the data of the name field and price field of the product table are found. For other codes, the data of the name field and price field are not found.
Using Right Join...On not only obtains the related field records in the two tables, but also includes all the records in the right table of the RIGHT JOIN. For example, the ASP program rs26.asp is as follows, [Select Sales.Code, Product.Name, Product .Price, Sales.Quantity from Sales Right Join Product On Product.Code = Sales.Code], using Right Join includes all records in the product table on the right side of RIGHT JOIN:
<%
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.Open "DBQ="& Server.MapPath("ntopsamp.mdb") &";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;"
Set rs3 = Server.CreateObject("ADODB.Recordset")
sql = "Select Sales.Code, Product.Name, Product.Price, Sales.Quantity from SalesRight Join ProductOn Product.Code = Sales.Code"
rs3.Open sql,conn1,1,1,1
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<TR>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Code</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Price</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000"><FONT COLOR="#FFFFFF">Quantity</FONT></TD>
</TR>
<% Do while not rs3.EOF %>
<TR>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(0)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(1)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(2)%></TD>
<TD BGCOLOR="f7efde" ALIGN=CENTER><%= rs3(3)%></TD>
<%
rs3.MoveNext
Loop
rs3.Close
%>
</TABLE>
For the above ASP program rs26.asp, use the browser on the client side to browse the execution results. The display contains all the records in the product table on the right side of the RIGHT JOIN. Since there is no quantity field in the product table, the product record with code number 4 is passed [ Sales Right Join Product On Product.Code = Sales.Code] The data in the quantity field of the sales table is found. For other codes, the data in the quantity field is not found.
Got it figured out?
