Using SELECT-SQL syntax in ASP's ADO (V)

Join

Join joins combine the field records in two tables, including three types:

INNER Join expression: A field record in a two-table connection combination.

Left JOIN expression: The join combines the field records from the two tables and contains all the records from the table on the left-hand side.

Right JOIN expression: The join combines the field records from the two tables and contains all the records in the right-hand table of the rights join.

The INNER join sets two table-related expressions to connect 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 Sub table 2. Field 2

Two table connected fields, such as Table 1. field 1= table 2. Field 2], you must have the same field type, but the field name does not need to be the same.

For example, an AutoNumber field type can connect a long field type, but a single integer numeric segment type cannot connect a field type of a double-precision integer.

The comparison operator can be =, <, >, <=, >=, or <>.

Join join fields that cannot contain Memo field types or OLE object types, otherwise an error occurs.

In a join expression, you can link multiple on clauses:

SELECT fields
From table 1 INNER JOIN table 2
On table 1. Field 1 comparison operator child table 2. Field 1 and
On table 1. Field 2 comparison operator child table 2. Field 2) OR
On table 1. Field 3 comparison operator child table 2. Field 3)

In a join expression, you 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 child table x. Field x)]
On table 2. Field 2 comparison operator child table 3. Field 3)
On table 1. Field 1 comparison operator child table 2. Field 2

In a inner JOIN, you can include a nested left JOIN or a right join, but you cannot include a nested inner join in a left JOIN or join.

Let's look at an example of using this SQL instruction in an ASP program.

You can use inner Join ... On combines records from two tables, such as ASP program rs26.asp as follows, [Select sales. Name, product, product, price, sales. Quantity from Sales Inner join product on product. Code = sales. Code number], use Inner join ... On sets two table-related expressions, combining records from two tables:

<%

Set conn1 = Server.CreateObject ("ADODB. Connection ")

Conn1. Open "dbq=" & Server.MapPath ("Ntopsamp.mdb") & ";D river={microsoft Access Driver (*.mdb)};D riverid=25;fil=ms Access; "

Set RS3 = Server.CreateObject ("ADODB. Recordset ")

sql = "Select Sales. Code, product, name, product, price, sales. Quantity from Sales Inner Join product on product. Code = sales.

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, 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 the browser in the client side, browses the execution result, displays the combination two tables the record, because the sales table does not have the Name field and the Price field, through [Inner Join product on product. Code = sales. Code NUMBER] Locate the Product Table Name field and the Price field data.

The previous example uses the syntax of the INNER join:

Select sales. Code, product, name, product, price, sales. Quantity from Sales Inner Join product on product. Code number = sales.

The result is the same as the following use where:

Select sales. Code, product, name, product, price, sales. Quantity from sales, product Where product. Code = sales.